//------------------------------------------------------------------------------
// <copyright company="Telligent Systems">
//     Copyright (c) Telligent Systems Corporation.  All rights reserved.
// </copyright> 
//------------------------------------------------------------------------------

using System;
using System.Collections;
using System.Collections.Specialized;
using System.Data;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using System.IO;
using System.Text;
using CommunityServer.Blogs.Components;
using CommunityServer.Components;
using CommunityServer.Discussions.Components;
using CommunityServer.SqlDataProvider;
using Content = CommunityServer.Components.Content;

namespace CommunityServer.Data 
{

    /// <summary>
    /// Summary description for WebForumsDataProvider
    /// </summary>
    public class SqlCommonDataProvider : CommonDataProvider {

        #region Member variables
        protected string databaseOwner	= "dbo";	// overwrite in web.config
        string connectionString = null;
        ProviderHelper sqlHelper = null;
        #endregion

        #region Constructor
        /****************************************************************
        // SqlDataProvider
        //
        /// <summary>
        /// Class constructor
        /// </summary>
        //
        ****************************************************************/
        public SqlCommonDataProvider(string databaseOwner, string connectionString) {

            // Read the connection string for this provider
            //
            this.connectionString = connectionString;

            // Read the database owner name for this provider
            //
            this.databaseOwner = databaseOwner;
             sqlHelper = ProviderHelper.Instance();

        }
        #endregion

        #region SettingsID 
        protected int GetSettingsID()
        {
            return  sqlHelper.GetSettingsID();
        }

        protected SqlParameter SettingsIDParameter()
        {
            SqlParameter p = new SqlParameter("@SettingsID",SqlDbType.Int);
            p.Value = GetSettingsID();
            return p;
        }
        #endregion
        
        #region Referrals

        public override void SaveReferralList(ArrayList referrals)
        {
            using( SqlConnection connection = GetSqlConnection() ) 
            {
                SqlCommand command = new SqlCommand( this.databaseOwner + ".cs_referrals_Add", connection);
                

                // Mark as stored procedure
                command.CommandType = CommandType.StoredProcedure;

                // Add parameters
                command.Parameters.Add("@SettingsID", SqlDbType.Int, 4);
                command.Parameters.Add("@SectionID", SqlDbType.Int, 4);
                command.Parameters.Add("@PostID", SqlDbType.Int, 4);
                command.Parameters.Add("@UrlID", SqlDbType.Int, 4);
                command.Parameters.Add("@Url", SqlDbType.NVarChar, 512);

                connection.Open();
                foreach(Referral referral in referrals)
                {

                    command.Parameters["@SettingsID"].Value = referral.SettingsID;
                    command.Parameters["@SectionID"].Value = referral.SectionID;
                    command.Parameters["@PostID"].Value = referral.PostID;
                    command.Parameters["@UrlID"].Value = referral.UrlID;
                    command.Parameters["@Url"].Value = referral.Url;
                    command.ExecuteNonQuery();
                }
                connection.Close();
                command.Dispose();
                connection.Dispose();
            }
        }

        public override ReferralSet GetReferrals(Referral r, int pageSize, int pageIndex)
        {
            using( SqlConnection connection = GetSqlConnection() ) 
            {
                SqlCommand command = new SqlCommand( this.databaseOwner + ".cs_referrals_Get", connection);

                command.CommandType = CommandType.StoredProcedure;

                // Add parameters
                command.Parameters.Add(this.SettingsIDParameter());
                command.Parameters.Add("@SectionID", SqlDbType.Int, 4).Value = r.SectionID;
                command.Parameters.Add("@PostID", SqlDbType.Int, 4).Value = r.PostID;
                command.Parameters.Add("@PageSize", SqlDbType.Int, 4).Value = SQLHelper.GetSafeSqlInt(pageSize);
                command.Parameters.Add("@PageIndex", SqlDbType.Int, 4).Value = pageIndex;

                SqlParameter p = new SqlParameter("@TotalRecords",SqlDbType.Int,4);
                p.Direction = ParameterDirection.Output;
                command.Parameters.Add(p);

                ReferralSet rs = new ReferralSet();
                Referral referral = null;
                connection.Open();
                SqlDataReader reader = command.ExecuteReader();
                while(reader.Read())
                {
                    referral = new Referral();
					referral.ReferralID = (int)reader["ReferralID"];
                    referral.PostID = (int)reader["PostID"];
                    referral.SectionID = (int)reader["SectionID"];
                    referral.LastDate = (DateTime)reader["LastDate"];
                    referral.Hits = (int)reader["Hits"];
                    referral.Url = reader["Url"] as string;
                    referral.Title = reader["Subject"] as string;
                    rs.Referrals.Add(referral);
                }

                reader.Close();
                rs.TotalRecords = (int)command.Parameters["@TotalRecords"].Value;

                command.Dispose();
                connection.Close();

                return rs;

            }


        }


        #endregion

        #region Deleted Post Archive

		public override void AuditDeletedPostContent(Post post)
		{
			using (SqlConnection connection = GetSqlConnection())
			{
				SqlCommand command = new SqlCommand(this.databaseOwner + ".cs_posts_AddAuditDeletedPostContent", connection);
				command.CommandType = CommandType.StoredProcedure;

				CSContext context = CSContext.Current;
				ApplicationType ap = post.Section != null ? post.Section.ApplicationType : ApplicationType.Unknown;

				command.Parameters.Add("@PostID", SqlDbType.Int, 4).Value = post.PostID;
				command.Parameters.Add("@UserID", SqlDbType.Int, 4).Value = context.User.UserID;
				command.Parameters.Add("@SectionID", SqlDbType.Int, 4).Value = post.SectionID;

				command.Parameters.Add("@PostType", SqlDbType.Int, 4).Value = post.PostType;
				command.Parameters.Add("@ThreadID", SqlDbType.Int, 4).Value = post.ThreadID;
				command.Parameters.Add("@PostLevel", SqlDbType.Int, 4).Value = post.PostLevel;
				command.Parameters.Add("@ParentID", SqlDbType.Int, 4).Value = post.ParentID;

				command.Parameters.Add("@SettingsID", SqlDbType.Int, 4).Value = context.SettingsID;
				command.Parameters.Add("@ApplicationType", SqlDbType.Int, 4).Value = ap;
				command.Parameters.Add("@Body", SqlDbType.NText).Value = post.Body;
				command.Parameters.Add("@Subject", SqlDbType.NVarChar, 500).Value = post.Subject;
				command.Parameters.Add("@IPAddress", SqlDbType.NVarChar, 50).Value = post.UserHostAddress;

				command.Parameters.Add("@PropertyNames", SqlDbType.NText).Value = post.GetSerializerData().Keys;
				command.Parameters.Add("@PropertyValues", SqlDbType.NText).Value = post.GetSerializerData().Values;
				command.Parameters.Add("@SpamScore", SqlDbType.Int, 4).Value = post.SpamScore;
				command.Parameters.Add("@PostStatus", SqlDbType.Int, 4).Value = post.PostStatus;

				connection.Open();
				command.ExecuteNonQuery();
				connection.Close();

			}
		}

        #endregion

        #region Views

        public override void SaveViewList(Hashtable views)
        {
            if(views == null || views.Count == 0)
                return;

            using( SqlConnection connection = GetSqlConnection() ) 
            {
                SqlCommand command = new SqlCommand( this.databaseOwner + ".cs_Posts_Views_Add", connection);

                command.CommandType = CommandType.StoredProcedure;

                command.Parameters.Add("@PostID", SqlDbType.Int, 4);
                command.Parameters.Add("@AggCount", SqlDbType.Int, 4);
                command.Parameters.Add("@WebCount", SqlDbType.Int, 4);

                View v = null;
                connection.Open();
                foreach(int postID in views.Keys)
                {
                    v = views[postID] as View;
                    if(v != null)
                    {
                        command.Parameters["@PostID"].Value = v.PostID;
                        command.Parameters["@AggCount"].Value = v.AggCount;
                        command.Parameters["@WebCount"].Value = v.WebCount;

                        command.ExecuteNonQuery();
                    }
                }
                command.Dispose();
                connection.Close();
            }
        }


        #endregion

		#region Private Messages
		public override int UnreadMessagesCount (int userID) 
		{

			HybridDictionary threads = new HybridDictionary();
			int unreadCount = 0;

			using(SqlConnection myConnection = GetSqlConnection()) 
			{
				SqlCommand myCommand = new SqlCommand(databaseOwner + ".cs_PrivateMessages_UnreadCount", myConnection);
				SqlDataReader reader;
				myCommand.CommandType = CommandType.StoredProcedure;
				myCommand.Parameters.Add("@UserID", SqlDbType.Int).Value = userID;
				myCommand.Parameters.Add(this.SettingsIDParameter());
				int readAfterID = 0;
                
				myConnection.Open();
				using(reader = myCommand.ExecuteReader(CommandBehavior.CloseConnection))
				{

					// Get the read items
					while (reader.Read()) 
					{
						if (( (int) reader["ThreadID"] ) > 0 )
						threads.Add( reader["ThreadID"], false );
					}

					// Have we marked all read?
					reader.NextResult();
					reader.Read();
					readAfterID = (int) reader["ThreadID"];
					if (readAfterID > 0) 
					{
						foreach (int i in threads.Keys) 
						{
							if (i >= readAfterID) 
							{
								threads[i] = true;
							}
						}
					}

					// Get the threads read for this section
					reader.NextResult();
					while (reader.Read()) 
					{
						if (threads[ reader["ThreadID"] ] != null)
							threads[ reader["ThreadID"] ] = true;
					}
					reader.Close();
				}

				myConnection.Close();

				foreach (int i in threads.Keys) 
				{
					if ( ((bool) threads[i]) == false)
						unreadCount++;

				}

			}		

			return unreadCount;
		}
		#endregion

		#region URL Redirects
		public override string LookupURL(double urlID) 
		{
			string url = null;

			using( SqlConnection connection = GetSqlConnection() ) 
			{
				SqlCommand command = new SqlCommand( this.databaseOwner + ".cs_Urls_RedirectLookup", connection);
				SqlDataReader reader;

				command.CommandType = CommandType.StoredProcedure;

				command.Parameters.Add("@UrlID", SqlDbType.Int).Value = urlID;

				connection.Open();
				reader = command.ExecuteReader();

				if (reader.Read())
					url = reader[0] as String;

				connection.Close();
			}

			return url;
		}

		public override UrlRedirect GetUrlRedirect(int urlID)
		{
			// Create Instance of Connection and Command Object
			using( SqlConnection myConnection = GetSqlConnection() ) 
			{
				SqlCommand myCommand = new SqlCommand(databaseOwner + ".cs_UrlRedirects_Get", myConnection);

				// Mark the Command as a SPROC
				myCommand.CommandType = CommandType.StoredProcedure;
				myCommand.Parameters.Add("@UrlID", SqlDbType.Int).Value = urlID;

				// Open the database connection and execute the command
				SqlDataReader dr;

				UrlRedirect urlRedirect = null;
				myConnection.Open();
				using(dr = myCommand.ExecuteReader(CommandBehavior.CloseConnection | CommandBehavior.SingleResult))
				{
					while (dr.Read())
						urlRedirect = PopulateUrlRedirectFromIDataReader(dr);

					dr.Close();
				}
				myConnection.Close();

				return urlRedirect;
			}
		}

		public override ArrayList GetUrlRedirects()
		{
			ArrayList urlRedirects = new ArrayList();

			// Create Instance of Connection and Command Object
			using( SqlConnection myConnection = GetSqlConnection() ) 
			{
				SqlCommand myCommand = new SqlCommand(databaseOwner + ".cs_UrlRedirects_Get", myConnection);

				// Mark the Command as a SPROC
				myCommand.CommandType = CommandType.StoredProcedure;

				// Open the database connection and execute the command
				SqlDataReader dr;

				myConnection.Open();
				using(dr = myCommand.ExecuteReader(CommandBehavior.CloseConnection | CommandBehavior.SingleResult))
				{
					while (dr.Read())
						urlRedirects.Add(PopulateUrlRedirectFromIDataReader(dr));

					dr.Close();
				}
				myConnection.Close();

				return urlRedirects;
			}
		}

		public override int CreateUpdateDeleteUrlRedirect(UrlRedirect urlRedirect, DataProviderAction action)
		{
			// Create Instance of Connection and Command Object
			using( SqlConnection myConnection = GetSqlConnection() ) 
			{
				SqlCommand myCommand = new SqlCommand(databaseOwner + ".cs_UrlRedirect_CreateUpdateDelete", myConnection);

				// Mark the Command as a SPROC
				myCommand.CommandType = CommandType.StoredProcedure;

				myCommand.Parameters.Add("@UrlID", SqlDbType.Int).Value = urlRedirect.ID;
				myCommand.Parameters.Add("@Url", SqlDbType.NVarChar, 4000).Value = urlRedirect.Url;
				myCommand.Parameters.Add("@Description", SqlDbType.NVarChar, 2000).Value = urlRedirect.Description;
				myCommand.Parameters.Add("@Action", SqlDbType.Int).Value = action;

				// Open the connection
				myConnection.Open();
				myCommand.ExecuteNonQuery();

				myConnection.Close();

				return 1;
			}
		}
		#endregion

        #region ApplicationSettings

        public override void CreateUpdateApplicationConfigurationSettings(ApplicationType appType, object obj)
        {
            using( SqlConnection connection = GetSqlConnection() ) 
            {
                SqlCommand command = new SqlCommand( this.databaseOwner + ".cs_ApplicationConfigurationSettings_CreateUpdate", connection);

                command.CommandType = CommandType.StoredProcedure;

                command.Parameters.Add(this.SettingsIDParameter());
                command.Parameters.Add("@ApplicationType", SqlDbType.SmallInt, 4).Value = appType;
                command.Parameters.Add("@Settings", SqlDbType.NText).Value = Serializer.ConvertToString(obj);
                connection.Open();
                command.ExecuteNonQuery();
                connection.Close();
            }

        }

        public override object GetApplicationConfigurationSettings(ApplicationType appType, Type objectType)
        {
            //cs_ApplicationConfigurationSettings_Get
            using( SqlConnection connection = GetSqlConnection() ) 
            {
                SqlCommand command = new SqlCommand( this.databaseOwner + ".cs_ApplicationConfigurationSettings_Get", connection);

                command.CommandType = CommandType.StoredProcedure;

                command.Parameters.Add(this.SettingsIDParameter());
                command.Parameters.Add("@ApplicationType", SqlDbType.SmallInt, 4).Value = appType;
                
                connection.Open();
                string xml = command.ExecuteScalar() as string;
                connection.Close();
                return Serializer.ConvertToObject(xml,objectType);

            }
        }


        #endregion

        #region Helper methods & properties
        protected SqlConnection GetSqlConnection () 
        {

            try {
                return new SqlConnection(ConnectionString);
            } catch {
                throw new CSException(CSExceptionType.DataProvider, "SQL Connection String is invalid.");
            }

        }

        public string ConnectionString {
            get {
                return connectionString;
            }
            set {
                connectionString = value;
            }
        }
        #endregion

        #region #### Site Settings ####
        public override ArrayList LoadAllSiteSettings() {
			using( SqlConnection connection = GetSqlConnection() ) {

				SqlCommand command = new SqlCommand( this.databaseOwner + ".cs_SiteSettings_Get", connection);
				SqlDataReader reader;
				ArrayList list = new ArrayList();

				// Mark as stored procedure
				command.CommandType = CommandType.StoredProcedure;

				// Add parameters
				command.Parameters.Add("@SiteUrl", SqlDbType.NVarChar, 512).Value ="*";
				command.Parameters.Add("@ReturnAll", SqlDbType.Bit, 1).Value = true;

				connection.Open();

                using(reader = command.ExecuteReader(CommandBehavior.CloseConnection))
                {
                    // We only expect a single record
                    while(reader.Read()) 
                    {
                        list.Add(PopulateSiteSettingsFromIDataReader(reader));
                    }

                    reader.Close();
                    // All done with the connection
                    //
                    connection.Close();
                }
				return list;
			}
        }

        /****************************************************************
        // LoadSiteSettings
        //
        /// <summary>
        /// Loads the site settings from the database
        /// </summary>
        //
        ****************************************************************/
        public override SiteSettings LoadSiteSettings(string application, int settingsID, bool returnAll) {
			using( SqlConnection connection = GetSqlConnection() ) {
				SqlCommand command = new SqlCommand(this.databaseOwner + ".cs_SiteSettings_Get", connection);
				SqlDataReader reader;
				SiteSettings settings = null;

				// Mark as stored procedure
				command.CommandType = CommandType.StoredProcedure;

				// Add parameters
				command.Parameters.Add("@SiteUrl", SqlDbType.NVarChar, 512).Value = application;
				command.Parameters.Add("@SettingsID", SqlDbType.Int,4).Value = settingsID;
				command.Parameters.Add("@ReturnAll", SqlDbType.Bit,1).Value = returnAll;

				try {
					connection.Open();
				} catch (SqlException sqlException) {
					throw new CSException(CSExceptionType.DataProvider, "Unable to open connection to data provider.", sqlException);
				}

                using(reader = command.ExecuteReader(CommandBehavior.CloseConnection))
                {

                    // Attempt to populate a site settings object
                    try 
                    {
                        if(reader.Read())
                        {
                            settings = PopulateSiteSettingsFromIDataReader(reader);
                        }
                    } 
                    catch (CSException ex) 
                    {
                        reader.Close();
                        connection.Close();

                        // Do we have bad XML?
                        if (ex.ExceptionType == CSExceptionType.SiteSettingsInvalidXML) 
                        {

                            settings = new SiteSettings();

                            settings.SettingsID = (int) reader["SettingsID"];
                            settings.SiteDomain = reader["SiteUrl"] as string;
                            settings.PublicSiteKey = (Guid)reader["SettingsKey"];
                            settings.ApplicationName = reader["ApplicationName"] as string;

                            // Save the updated values
                            SaveSiteSettings(settings);

                        }
                    }
                    finally
                    {
                        reader.Close();
                        connection.Close();
                    }
                }

				return settings;
			}
        }

        /****************************************************************
        // SaveSiteSettings
        //
        /// <summary>
        /// Save the site settings from the database
        /// </summary>
        //
        ****************************************************************/
        public override void SaveSiteSettings(SiteSettings siteSettings) {
//            BinaryFormatter binaryFormatter = new BinaryFormatter();
//            MemoryStream ms = new MemoryStream();
//            byte[] b;

			using( SqlConnection connection = GetSqlConnection() ) {
				SqlCommand command = new SqlCommand(this.databaseOwner + ".cs_SiteSettings_Save", connection);
				command.CommandType = CommandType.StoredProcedure;

				// Set the parameters
				//
                command.Parameters.Add("@ApplicationName", SqlDbType.NVarChar, 256).Value = siteSettings.ApplicationName;
				command.Parameters.Add("@Disabled", SqlDbType.SmallInt).Value = false;//not used
                command.Parameters.Add("@SettingsXML", SqlDbType.NText).Value = Serializer.ConvertToString(siteSettings);
                command.Parameters.Add("@SettingsID",SqlDbType.Int,4).Value = siteSettings.SettingsID;

				// Open the connection and exectute
				//
				connection.Open();
				command.ExecuteNonQuery();
				connection.Close();

			}
		
//			binaryFormatter = null;
//			ms = null;
		}

		public override Hashtable GetSiteUrls()
		{
			using( SqlConnection connection = GetSqlConnection() ) 
			{
				SqlCommand command = new SqlCommand(this.databaseOwner + ".cs_Site_GetSiteUrls", connection);
				Hashtable siteUrls = new Hashtable();

				// Mark as stored procedure
				command.CommandType = CommandType.StoredProcedure;

				connection.Open();
				using(SqlDataReader reader = command.ExecuteReader(CommandBehavior.CloseConnection))
				{
					while (reader.Read())
					{
						string applicationName = (string)reader["ApplicationName"];
						string siteUrl = (string)reader["SiteUrl"];

						if (!siteUrls.Contains(applicationName))
							siteUrls.Add(applicationName, new ArrayList());

						((ArrayList)siteUrls[applicationName]).Add(siteUrl);
					}
				}

				return siteUrls;
			}
		}

        #endregion

        #region #### Search ####
        /****************************************************************
        // GetSearchIgnoreWords
        //
        /// <summary>
        /// Loads the lexicon of words used by search.
        /// </summary>
        //
        ****************************************************************/
        public override Hashtable GetSearchIgnoreWords(int settingsID) {
            Hashtable ignoreWords = new Hashtable();

            // Create Instance of Connection and Command Object
			using( SqlConnection myConnection = GetSqlConnection() ) {
				SqlCommand myCommand = new SqlCommand(databaseOwner + ".cs_Search_IgnoreWords", myConnection);
				SqlDataReader reader;

				// Mark the Command as a SPROC
				myCommand.CommandType = CommandType.StoredProcedure;

                myCommand.Parameters.Add("@SettingsID", SqlDbType.Int,4).Value = settingsID;

				// Execute the command
				myConnection.Open();

                using(reader = myCommand.ExecuteReader(CommandBehavior.CloseConnection))
                {
                    // Are we loading a lexicon for ignore word?
                    //
                    while (reader.Read())
                        ignoreWords.Add( Convert.ToInt32(reader["WordHash"]), (string) reader["Word"] );

                    reader.Close();
                    myConnection.Close();
                }

				return ignoreWords;       
			}
        }

        public override void CreateDeleteSearchIgnoreWords (ArrayList words, DataProviderAction action) {

            // Create Instance of Connection and Command Object
			using( SqlConnection myConnection = GetSqlConnection() ) {
				SqlCommand myCommand = new SqlCommand(databaseOwner + ".cs_Search_IgnoreWords_CreateDelete", myConnection);

				// Mark the Command as a SPROC
				myCommand.CommandType = CommandType.StoredProcedure;
				myCommand.Parameters.Add("@WordHash", SqlDbType.Int);
				myCommand.Parameters.Add("@Word", SqlDbType.NVarChar, 64);
				myCommand.Parameters.Add("@Action", SqlDbType.Int).Value = action;
                myCommand.Parameters.Add(this.SettingsIDParameter());

				// Execute the command
				myConnection.Open();

				foreach (string word in words) {
					myCommand.Parameters["@WordHash"].Value = word.GetHashCode();
					myCommand.Parameters["@Word"].Value = word;

					myCommand.ExecuteNonQuery();
				}

				myConnection.Close();
			}
        }
        
		public override void UpdatePostIndexStatus(int settingsID, int[] postids, PostSearchStatus status)
		{
			using( SqlConnection myConnection = GetSqlConnection() ) 
			{
				SqlCommand myCommand = new SqlCommand(databaseOwner + ".cs_Search_UpdatePostIndexStatus", myConnection);

				// Mark the Command as a SPROC
				//
				myCommand.CommandType = CommandType.StoredProcedure;

				// Execute the command
				//
				myConnection.Open();
            
				myCommand.Parameters.Add("@SettingsID",SqlDbType.Int,4).Value = settingsID;
				myCommand.Parameters.Add("@PostID", SqlDbType.Int);
                myCommand.Parameters.Add("@Status", SqlDbType.Bit).Value = status;
            
				foreach (int postid in postids) 
				{
					myCommand.Parameters["@PostID"].Value = postid;
					myCommand.ExecuteNonQuery();
				}

				// Ensure we tear down the connection and command
				myConnection.Close();
				myCommand.Dispose();
				

			}
		}
        
        public override void InsertIntoSearchBarrel (Hashtable words, Post post, int settingsID) {

            // Create Instance of Connection and Command Object
            //
			using( SqlConnection myConnection = GetSqlConnection() ) {
				SqlCommand myCommand = new SqlCommand(databaseOwner + ".cs_Search_Add", myConnection);

				// Mark the Command as a SPROC
				//
				myCommand.CommandType = CommandType.StoredProcedure;

				// Execute the command
				//
				myConnection.Open();
            
                myCommand.Parameters.Add("@SettingsID",SqlDbType.Int,4).Value = settingsID;

                // Set up the parameters >> Leave out the values, since we will get them on the loop
                myCommand.Parameters.Add("@WordHash", SqlDbType.Int);
                myCommand.Parameters.Add("@Word", SqlDbType.NVarChar, 64);
                myCommand.Parameters.Add("@Weight", SqlDbType.Float);
                myCommand.Parameters.Add("@PostID", SqlDbType.Int);
                myCommand.Parameters.Add("@ThreadID", SqlDbType.Int);
                myCommand.Parameters.Add("@SectionID", SqlDbType.Int);
            
				foreach (int wordHash in words.Keys) {
					Word word;

					// Get the Word instance to process
					//
					word = (Word) words[wordHash];



						myCommand.Parameters["@WordHash"].Value = wordHash;
						myCommand.Parameters["@Word"].Value = word.Name;
						myCommand.Parameters["@Weight"].Value = word.Weight;
						myCommand.Parameters["@PostID"].Value = post.PostID;
						myCommand.Parameters["@ThreadID"].Value = post.ThreadID;
						myCommand.Parameters["@SectionID"].Value = post.SectionID;

					myCommand.ExecuteNonQuery();
				}

				// Ensure we tear down the connection and command
				myConnection.Close();
                myCommand.Dispose();
				

			}
        }
        #endregion

        #region #### Post ####

		public override bool IsThreadTracked(int threadID, int userID)
		{
			//cs_thread_IsTracked
			using(SqlConnection myConnection = GetSqlConnection()) 
			{
				SqlCommand myCommand = new SqlCommand(databaseOwner + ".cs_thread_IsTracked", myConnection);
				myCommand.CommandType = CommandType.StoredProcedure;
				myCommand.Parameters.Add("@ThreadID", SqlDbType.Int).Value = threadID;
				myCommand.Parameters.Add("@UserID", SqlDbType.Int).Value = userID;
				myCommand.Parameters.Add(this.SettingsIDParameter());
				myCommand.Parameters.Add("@IsTracked", SqlDbType.Bit).Direction = ParameterDirection.Output;
                
				myConnection.Open();
				myCommand.ExecuteNonQuery();
				myConnection.Close();

				return (bool)myCommand.Parameters["@IsTracked"].Value;
			}
		}

        #endregion

		#region Rating
		public override void Rate(Rating rating)
		{
			using (SqlConnection connection = GetSqlConnection())
			{
				SqlCommand command = new SqlCommand(databaseOwner + ".cs_Rating_Rate", connection);
				command.CommandType = CommandType.StoredProcedure;

				command.Parameters.Add("@RatingType", SqlDbType.Int).Value = rating.RatingType;
				command.Parameters.Add("@ItemID", SqlDbType.Int).Value = rating.ItemID;
				command.Parameters.Add("@UserID", SqlDbType.Int).Value = rating.User.UserID;
				command.Parameters.Add("@Rating", SqlDbType.Int).Value = rating.Value;
				command.Parameters.Add(this.SettingsIDParameter());
                
				connection.Open();
				command.ExecuteNonQuery();
				connection.Close();
            }
        }

		public override int GetRating(RatingType ratingType, int itemID, int userID)
		{
			using (SqlConnection connection = GetSqlConnection())
			{
				SqlCommand command = new SqlCommand(databaseOwner + ".cs_Rating_Get", connection);
				command.CommandType = CommandType.StoredProcedure;

				command.Parameters.Add("@RatingType", SqlDbType.Int).Value = ratingType;
				command.Parameters.Add("@ItemID", SqlDbType.Int).Value = itemID;
				command.Parameters.Add("@UserID", SqlDbType.Int).Value = userID;
				command.Parameters.Add(this.SettingsIDParameter());
				command.Parameters.Add("@Rating", SqlDbType.Int).Direction = ParameterDirection.Output;

				connection.Open();
				command.ExecuteNonQuery();
				int rating = (int)command.Parameters["@Rating"].Value;
				connection.Close();

				return rating;
			}
		}

		public override IList GetRatings(RatingType ratingType, int itemID)
		{
			IList ratings = new ArrayList();

			using (SqlConnection connection = GetSqlConnection())
			{
				SqlCommand command = new SqlCommand(databaseOwner + ".cs_Ratings_Get", connection);
				command.CommandType = CommandType.StoredProcedure;

				command.Parameters.Add("@RatingType", SqlDbType.Int).Value = ratingType;
				command.Parameters.Add("@ItemID", SqlDbType.Int).Value = itemID;
				command.Parameters.Add(this.SettingsIDParameter());

                SqlDataReader dr;
				connection.Open();
				using (dr = command.ExecuteReader(CommandBehavior.CloseConnection))
				{
					while (dr.Read())
					{
						ratings.Add(PopulateRatingFromIDataReader(dr));
					}
					dr.Close();
				}
				connection.Close();
			}
                
			return ratings;
		}
		#endregion

		#region Favorites
		public override int AddFavorite(Favorite favorite)
                {
			using (SqlConnection connection = GetSqlConnection())
			{
				SqlCommand command = new SqlCommand(databaseOwner + ".cs_Favorite_Add", connection);
				command.CommandType = CommandType.StoredProcedure;

				command.Parameters.Add(this.SettingsIDParameter());
				command.Parameters.Add("@UserID", SqlDbType.Int).Value = favorite.UserID;
				command.Parameters.Add("@ItemID", SqlDbType.Int).Value = favorite.ItemID;
				command.Parameters.Add("@FavoriteType", SqlDbType.Int).Value = favorite.FavoriteType;
				command.Parameters.Add("@ApplicationType", SqlDbType.Int).Value = favorite.ApplicationType;
				command.Parameters.Add("@FavoriteID", SqlDbType.Int).Direction = ParameterDirection.Output;

				connection.Open();
				command.ExecuteNonQuery();
				int favoriteID = (int)command.Parameters["@FavoriteID"].Value;
				connection.Close();

				return favoriteID;
			}
		}

		public override void DeleteFavorite(Favorite favorite)
		{
			using (SqlConnection connection = GetSqlConnection())
			{
				SqlCommand command = new SqlCommand(databaseOwner + ".cs_Favorite_Delete", connection);
				command.CommandType = CommandType.StoredProcedure;

				command.Parameters.Add("@FavoriteType", SqlDbType.Int).Value = favorite.FavoriteType;
				command.Parameters.Add("@FavoriteID", SqlDbType.Int).Value = favorite.FavoriteID;

				connection.Open();
				command.ExecuteNonQuery();
				connection.Close();
			}
		}

		public override Favorite GetFavorite(FavoriteType favoriteType, int favoriteID)
		{
			using (SqlConnection connection = GetSqlConnection())
			{
				SqlCommand command = new SqlCommand(databaseOwner + ".cs_Favorite_Get", connection);
				command.CommandType = CommandType.StoredProcedure;

				command.Parameters.Add("@FavoriteType", SqlDbType.Int).Value = favoriteType;
				command.Parameters.Add("@FavoriteID", SqlDbType.Int).Value = favoriteID;

				Favorite favorite = null;
				connection.Open();
				using (SqlDataReader dr = command.ExecuteReader(CommandBehavior.CloseConnection))
				{
                    while (dr.Read()) 
                    {
						favorite = PopulateFavoriteFromIDataReader(dr);
                    }
                    dr.Close();
                }
				connection.Close();

				return favorite;
			}
            }

		public override Favorite GetFavorite(FavoriteType favoriteType, int userID, int itemID)
		{
			using (SqlConnection connection = GetSqlConnection())
			{
				SqlCommand command = new SqlCommand(databaseOwner + ".cs_Favorite_GetByUserItem", connection);
				command.CommandType = CommandType.StoredProcedure;

				command.Parameters.Add(this.SettingsIDParameter());
				command.Parameters.Add("@FavoriteType", SqlDbType.Int).Value = favoriteType;
				command.Parameters.Add("@UserID", SqlDbType.Int).Value = userID;
				command.Parameters.Add("@ItemID", SqlDbType.Int).Value = itemID;

				Favorite favorite = null;
				connection.Open();
				using (SqlDataReader dr = command.ExecuteReader(CommandBehavior.CloseConnection))
				{
					while (dr.Read())
					{
						favorite = PopulateFavoriteFromIDataReader(dr);
					}
					dr.Close();
				}
				connection.Close();

				return favorite;
			}
		}


		public override IList GetFavorites(FavoriteType favoriteType, int userID)
		{
			IList favorites = new ArrayList();

			using (SqlConnection connection = GetSqlConnection())
			{
				SqlCommand command = new SqlCommand(databaseOwner + ".cs_Favorites_Get", connection);
				command.CommandType = CommandType.StoredProcedure;

				command.Parameters.Add("@FavoriteType", SqlDbType.Int).Value = favoriteType;
				command.Parameters.Add("@UserID", SqlDbType.Int).Value = userID;
				command.Parameters.Add(this.SettingsIDParameter());

				SqlDataReader dr;
				connection.Open();
				using (dr = command.ExecuteReader(CommandBehavior.CloseConnection))
				{
					while (dr.Read())
					{
						favorites.Add(PopulateFavoriteFromIDataReader(dr));
					}
					dr.Close();
				}
				connection.Close();
			}

			return favorites;
		}

		public override IList GetFavoritePosts(int userID)
		{
			IList posts = new ArrayList();

			using (SqlConnection connection = GetSqlConnection())
			{
				SqlCommand command = new SqlCommand(databaseOwner + ".cs_Favorites_GetPosts", connection);
				command.CommandType = CommandType.StoredProcedure;

				command.Parameters.Add("@UserID", SqlDbType.Int).Value = userID;
				command.Parameters.Add(this.SettingsIDParameter());

				SqlDataReader dr;
				connection.Open();
				using (dr = command.ExecuteReader(CommandBehavior.CloseConnection))
				{
					while (dr.Read())
					{
						ApplicationType appType = (ApplicationType)Convert.ToInt32(dr["ApplicationType"]);
						Post post = ApplicationSet.Applications[appType].InstantiatePost(dr);
						Section section = ApplicationSet.Applications[appType].GetSectionByID(post.SectionID);
						if (section != null)
						{
							post.Section = section;
							posts.Add(post);
						}
					}
					dr.Close();
				}
				connection.Close();
			}

			return posts;
		}

		public override IList GetFavoriteSections(int userID)
		{
			IList sections = new ArrayList();

			using (SqlConnection connection = GetSqlConnection())
			{
				SqlCommand command = new SqlCommand(databaseOwner + ".cs_Favorites_GetSections", connection);
				command.CommandType = CommandType.StoredProcedure;

				command.Parameters.Add("@UserID", SqlDbType.Int).Value = userID;
				command.Parameters.Add(this.SettingsIDParameter());

				SqlDataReader dr;
				connection.Open();
				using (dr = command.ExecuteReader(CommandBehavior.CloseConnection))
				{
					while (dr.Read())
					{
						ApplicationType appType = (ApplicationType)Convert.ToInt32(dr["ApplicationType"]);
						int sectionID = (int)dr["SectionID"];
						Section section = ApplicationSet.Applications[appType].GetSectionByID(sectionID);
						if (section != null)
							sections.Add(section);
					}
					dr.Close();
				}
				connection.Close();
			}

			return sections;
		}

		public override IList GetFavoriteUsers(int userID)
		{
			IList users = new ArrayList();

			using (SqlConnection connection = GetSqlConnection())
			{
				SqlCommand command = new SqlCommand(databaseOwner + ".cs_Favorites_GetUsers", connection);
				command.CommandType = CommandType.StoredProcedure;

				command.Parameters.Add("@UserID", SqlDbType.Int).Value = userID;
				command.Parameters.Add(this.SettingsIDParameter());

				SqlDataReader dr;
				connection.Open();
				using (dr = command.ExecuteReader(CommandBehavior.CloseConnection))
				{
					while (dr.Read())
					{
						users.Add(cs_PopulateUserFromIDataReader(dr));
					}
					dr.Close();
				}
				connection.Close();
			}

			return users;
		}

		public override IList GetFavoriteUsersWatching(int userID)
		{
			IList users = new ArrayList();

			using (SqlConnection connection = GetSqlConnection())
			{
				SqlCommand command = new SqlCommand(databaseOwner + ".cs_Favorites_GetUsersWatching", connection);
				command.CommandType = CommandType.StoredProcedure;

				command.Parameters.Add("@UserID", SqlDbType.Int).Value = userID;
				command.Parameters.Add(this.SettingsIDParameter());

				SqlDataReader dr;
				connection.Open();
				using (dr = command.ExecuteReader(CommandBehavior.CloseConnection))
				{
					while (dr.Read())
					{
						users.Add(cs_PopulateUserFromIDataReader(dr));
					}
					dr.Close();
				}
				connection.Close();
			}

			return users;
        }
        #endregion

        #region EventLogs

		public override EventLogEntry GetEventLogEntry(int entryID)
		{
			using (SqlConnection connection = GetSqlConnection())
			{
				SqlCommand command = new SqlCommand(databaseOwner + ".cs_EventLog_GetEntry", connection);
				command.CommandType = CommandType.StoredProcedure;

				command.Parameters.Add("@EntryID", SqlDbType.Int).Value = entryID;

				EventLogEntry entry = null;
				connection.Open();
				using (SqlDataReader dr = command.ExecuteReader(CommandBehavior.CloseConnection))
				{
					while (dr.Read())
						entry = CommonDataProvider.PopulateEventLogEntryFromIDataReader(dr);

					dr.Close();
				}
				connection.Close();

				return entry;
			}
		}

		public override ArrayList GetEventLogEntries()
		{
			using (SqlConnection connection = GetSqlConnection())
			{
				SqlCommand command = new SqlCommand(databaseOwner + ".cs_EventLog_GetEntries", connection);
				command.CommandType = CommandType.StoredProcedure;

				command.Parameters.Add(this.SettingsIDParameter());

				ArrayList entries = new ArrayList();
				connection.Open();
				using (SqlDataReader dr = command.ExecuteReader(CommandBehavior.CloseConnection))
				{
					while (dr.Read())
						entries.Add(CommonDataProvider.PopulateEventLogEntryFromIDataReader(dr));

					dr.Close();
				}
				connection.Close();

				return entries;
			}
		}

        public override void WriteEventLogEntry(EventLogEntry entry)
        {
            using (SqlConnection connection = GetSqlConnection())
            {
                SqlCommand command = new SqlCommand(databaseOwner + ".cs_EventLog_Add", connection);
                command.CommandType = CommandType.StoredProcedure;

                command.Parameters.Add("@EventType", SqlDbType.Int).Value = entry.EventType;
                command.Parameters.Add("@EventID", SqlDbType.Int).Value = entry.EventID;
                command.Parameters.Add("@SettingsID", SqlDbType.Int).Value = entry.SettingsID;
                command.Parameters.Add("@Message", SqlDbType.NText).Value = entry.Message;
                command.Parameters.Add("@Category", SqlDbType.NVarChar).Value = entry.Category;
                command.Parameters.Add("@MachineName", SqlDbType.NVarChar).Value = entry.MachineName;

                connection.Open();
                command.ExecuteNonQuery();
                connection.Close();
            }
        }

        public override void ClearEventLog(DateTime date)
        {
            using (SqlConnection connection = GetSqlConnection())
            {
                SqlCommand command = new SqlCommand(databaseOwner + ".cs_EventLog_Clear", connection);
                command.CommandType = CommandType.StoredProcedure;

                command.Parameters.Add("@Date", SqlDbType.DateTime).Value = date;

                connection.Open();
                command.ExecuteNonQuery();
                connection.Close();
            }
        }

        #endregion 

        #region #### Exceptions and Tracing ####

        public override void LogException (CSException exception, int settingsID) 
		{

            try
            {
                using(SqlConnection myConnection = GetSqlConnection()) 
                {
                    SqlCommand myCommand = new SqlCommand(databaseOwner + ".cs_Exceptions_Log", myConnection);
                    myCommand.CommandType = CommandType.StoredProcedure;

                    myCommand.Parameters.Add("@SettingsID", SqlDbType.Int,4).Value = settingsID;
                    myCommand.Parameters.Add("@ExceptionHash", SqlDbType.VarChar, 128).Value = exception.GetHashCode(settingsID);
                    myCommand.Parameters.Add("@Category", SqlDbType.Int).Value = exception.Category;
                    myCommand.Parameters.Add("@Exception", SqlDbType.NVarChar, 2000).Value = exception.GetBaseException().ToString();
                    myCommand.Parameters.Add("@ExceptionMessage", SqlDbType.NVarChar, 500).Value = exception.Message;
                    myCommand.Parameters.Add("@UserAgent", SqlDbType.NVarChar, 64).Value = exception.UserAgent;
                    myCommand.Parameters.Add("@IPAddress", SqlDbType.VarChar, 15).Value = exception.IPAddress;
                    myCommand.Parameters.Add("@HttpReferrer", SqlDbType.NVarChar, 512).Value = exception.HttpReferrer;
                    myCommand.Parameters.Add("@HttpVerb", SqlDbType.NVarChar, 24).Value = exception.HttpVerb;
                    myCommand.Parameters.Add("@PathAndQuery", SqlDbType.NVarChar, 512).Value = exception.HttpPathAndQuery;
                
                    myConnection.Open();
                    myCommand.ExecuteNonQuery();
			
                    // Close the connection
                    myConnection.Close();
                }
            }
            catch{} //Not much to do if exception logging fails

        }

        public override void DeleteExceptions(int settingsID, ArrayList deleteList) {
			using( SqlConnection myConnection = GetSqlConnection() ) {
				StringBuilder sql = new StringBuilder();

				sql.Append("DELETE " + this.databaseOwner + ".cs_Exceptions WHERE SettingsID = " + settingsID);

				if ((deleteList != null) && (deleteList.Count > 0)) {

					sql.Append(" AND (");

					for (int i = 0; i < deleteList.Count; i++) {
						sql.Append("ExceptionID = ");
						sql.Append( deleteList[i].ToString());

						if ((i+1) != deleteList.Count) {
							sql.Append(" OR ");
						} else {
							sql.Append(")");
						}
					}
				}

				SqlCommand myCommand = new SqlCommand(sql.ToString(), myConnection);

				// Execute the command
				myConnection.Open();
				myCommand.ExecuteNonQuery();
				myConnection.Close();
			}
        }


		public override ArrayList GetExceptions (int exceptionType, int minFrequency, int sortOrder, bool includeUnknown) {
			ArrayList exceptions = new ArrayList();

			using(SqlConnection myConnection = GetSqlConnection()) {
				SqlCommand myCommand = new SqlCommand(databaseOwner + ".cs_Exceptions_Get", myConnection);
				myCommand.CommandType = CommandType.StoredProcedure;
				SqlDataReader reader;

				myCommand.Parameters.Add(this.SettingsIDParameter());
				myCommand.Parameters.Add("@ExceptionType", SqlDbType.Int).Value = exceptionType;
				myCommand.Parameters.Add("@MinFrequency", SqlDbType.Int).Value = minFrequency;
				myCommand.Parameters.Add("@SortOrder", SqlDbType.Int).Value = sortOrder;
				myCommand.Parameters.Add("@IncludeUnknown", SqlDbType.Bit).Value = includeUnknown;
                
				myConnection.Open();

                using(reader = myCommand.ExecuteReader(CommandBehavior.CloseConnection))
                {
                    while (reader.Read()) 
                    {
                        exceptions.Add( PopulateForumExceptionFromIDataReader(reader) );
                    }
                    reader.Close();
                }
				// Close the connection
				myConnection.Close();

				return exceptions;
			}
        }
        #endregion
        
        #region Disallowed Names
        /// <summary>
        /// Retrieves the collection of disallowed names.
        /// <returns>An ArrayList object.</returns>
        /// </summary>
        public override ArrayList GetDisallowedNames()
        {
          using( SqlConnection myConnection = GetSqlConnection() )  {

            SqlCommand myCommand = new SqlCommand(databaseOwner + ".cs_DisallowedNames_Get", myConnection);
            myCommand.CommandType = CommandType.StoredProcedure;
            myCommand.Parameters.Add(this.SettingsIDParameter());
            ArrayList names = null;

            // Execute the command
            myConnection.Open();
              using(SqlDataReader dr = myCommand.ExecuteReader(CommandBehavior.CloseConnection))
              {
              
                  names = new ArrayList();
                  while ( dr.Read() )
                      names.Add( Convert.ToString(dr["DisallowedName"]) );

                  dr.Close();
                  myConnection.Close();
              }

            return names;
          }
        }

        /// <summary>
        /// Generic method to create, update and delete a disallowed name.
        /// <param name="name">The name that will be added, deleted or update the old name.</param>
        /// <param name="replacement">The name that will be updated.</param>
        /// <param name="action">Datastore operation: create, update or delete.</param>
        /// <returns>Operation status: true on success, otherwise false.</returns>
        /// </summary>
        public override int CreateUpdateDeleteDisallowedName(string name, string replacement, DataProviderAction action)
        {
          using( SqlConnection myConnection = GetSqlConnection() ) {

            SqlCommand myCommand = new SqlCommand(databaseOwner + ".cs_DisallowedName_CreateUpdateDelete", myConnection);
            myCommand.CommandType = CommandType.StoredProcedure;

              myCommand.Parameters.Add(this.SettingsIDParameter());

            // Set the parameters
            //
            if(action == DataProviderAction.Delete)
              myCommand.Parameters.Add("@DeleteName", SqlDbType.Bit).Value = 1;            
            else
              myCommand.Parameters.Add("@DeleteName", SqlDbType.Bit).Value = 0;            

            if(action == DataProviderAction.Update) {
              myCommand.Parameters.Add("@Name", SqlDbType.NVarChar, 64).Value = name;
              myCommand.Parameters.Add("@Replacement", SqlDbType.NVarChar, 64).Value = replacement;
            }
            else {
              myCommand.Parameters.Add("@Name", SqlDbType.NVarChar, 64).Value = name;
              myCommand.Parameters.Add("@Replacement", SqlDbType.NVarChar, 64).Value = DBNull.Value;
            }

            myConnection.Open();              
            // Handle duplicate values
            try {
            myCommand.ExecuteNonQuery();
            }
            catch { }
              
            myConnection.Close();            
            return 1;
          }
        }
        #endregion

        #region Resources
        public override void CreateUpdateDeleteAvatar (int userID, Avatar avatar, DataProviderAction action) 
        {
            using(SqlConnection myConnection = GetSqlConnection()) {
                SqlCommand myCommand = new SqlCommand(databaseOwner + ".cs_User_Avatar_CreateUpdateDelete", myConnection);
                myCommand.CommandType = CommandType.StoredProcedure;
                myCommand.Parameters.Add("@UserID", SqlDbType.Int).Value = userID;
                myCommand.Parameters.Add("@Content", SqlDbType.Image).Value = avatar.Content;
                myCommand.Parameters.Add("@ContentType", SqlDbType.NVarChar, 64).Value = avatar.ContentType;
                myCommand.Parameters.Add("@ContentSize", SqlDbType.Int).Value = avatar.Length;
                myCommand.Parameters.Add("@Action", SqlDbType.Int).Value            = action;
                myCommand.Parameters.Add(this.SettingsIDParameter());
                
                myConnection.Open();

                myCommand.ExecuteNonQuery();

                myConnection.Close();
            }

        }
        #endregion

        #region Categories


        public override  int CreateCategory(PostCategory category)
        {
            using(SqlConnection myConnection = GetSqlConnection())
            {
                SqlCommand myCommand = new SqlCommand(databaseOwner + ".cs_PostCategory_CreateUpdateDelete", myConnection);
                myCommand.CommandType = CommandType.StoredProcedure;

                // Add the parameters
                myCommand.Parameters.Add("@SectionID", SqlDbType.Int).Value = category.SectionID;
                myCommand.Parameters.Add("@Name", SqlDbType.NVarChar, 256).Value = category.Name;
                myCommand.Parameters.Add("@IsEnabled", SqlDbType.Bit).Value = category.IsEnabled;
                myCommand.Parameters.Add("@ParentID", SqlDbType.Int).Value = category.ParentID;
                myCommand.Parameters.Add("@Description", SqlDbType.NVarChar, 2000).Value = category.Description;

				if(category.FeaturedPostID != -1)
					myCommand.Parameters.Add("@FeaturedPostID", SqlDbType.Int).Value = category.FeaturedPostID;

                // We want to get the CategoryID back
                myCommand.Parameters.Add("@CategoryID", SqlDbType.Int).Direction = ParameterDirection.Output;
				myCommand.Parameters.Add(this.SettingsIDParameter());

                // Execute the command
                myConnection.Open();
                int count = myCommand.ExecuteNonQuery();
                myConnection.Close();

                if(count > 0)
                {
                    try
                    {
                        return (int)myCommand.Parameters["@CategoryID"].Value;
						
                    }
                    catch
                    {
                        
                    }
                }

                return -1;
            }
        }


        public override  bool UpdateCategory(PostCategory category)
        {
            using(SqlConnection myConnection = GetSqlConnection())
            {
                SqlCommand myCommand = new SqlCommand(databaseOwner + ".cs_PostCategory_CreateUpdateDelete", myConnection);
                myCommand.CommandType = CommandType.StoredProcedure;

                // Add the parameters
                myCommand.Parameters.Add("@CategoryID", SqlDbType.Int).Value = category.CategoryID;
                myCommand.Parameters.Add("@SectionID", SqlDbType.Int).Value = category.SectionID;
                myCommand.Parameters.Add("@Name", SqlDbType.NVarChar, 256).Value = category.Name;
                myCommand.Parameters.Add("@IsEnabled", SqlDbType.Bit).Value = category.IsEnabled;
                myCommand.Parameters.Add("@ParentID", SqlDbType.Int).Value = category.ParentID;
                myCommand.Parameters.Add("@Description", SqlDbType.NVarChar, 2000).Value = category.Description;
				myCommand.Parameters.Add(this.SettingsIDParameter());

				if(category.FeaturedPostID != -1)
					myCommand.Parameters.Add("@FeaturedPostID", SqlDbType.Int).Value = category.FeaturedPostID;

                // Execute the command
                myConnection.Open();
                int count = myCommand.ExecuteNonQuery();
                myConnection.Close();

				return (count > 0);
            }
        }


        public override  void DeleteCategory(int categoryID, int forumID)
        {
            using(SqlConnection myConnection = GetSqlConnection())
            {
                SqlCommand myCommand = new SqlCommand(databaseOwner + ".cs_PostCategory_CreateUpdateDelete", myConnection);
                myCommand.CommandType = CommandType.StoredProcedure;

                // Add the parameters
                myCommand.Parameters.Add("@CategoryID", SqlDbType.Int).Value = categoryID;
                myCommand.Parameters.Add("@SectionID", SqlDbType.Int).Value = forumID;
                myCommand.Parameters.Add("@DeleteCategory", SqlDbType.Bit).Value = true;
				myCommand.Parameters.Add(this.SettingsIDParameter());

                // Execute the command
                myConnection.Open();
                myCommand.ExecuteNonQuery();
                myConnection.Close();
            }
        }


        public override Hashtable GetCategories(int forumID)
        {
            using( SqlConnection connection = GetSqlConnection() ) 
            {
                SqlCommand command = new SqlCommand(databaseOwner + ".cs_PostCategories_Get", connection);
                command.CommandType = CommandType.StoredProcedure;
                Hashtable categories = new Hashtable();

                // Add Parameters to SPROC
                //
                command.Parameters.Add("@SectionID", SqlDbType.Int).Value = forumID;

                // Execute the command
                connection.Open();
                using(SqlDataReader dr = command.ExecuteReader(CommandBehavior.CloseConnection))
                {

                    // Populate the Hashtable
                    while(dr.Read()) 
                    {
                        // Add categories
                        PostCategory category = new PostCategory();

                        category.CategoryID =			(int) dr["CategoryID"];
                        category.SectionID =			(int) dr["SectionID"];
                        category.Name =					(string) dr["Name"];
                        category.IsEnabled =			(bool) dr["IsEnabled"];
                        category.ParentID =				(int) dr["ParentID"];
                        category.Path =					(string) dr["Path"];
						category.DateCreated =				(DateTime) dr["DateCreated"];

						if(dr["FeaturedPostID"] != DBNull.Value)
							category.FeaturedPostID =		(int) dr["FeaturedPostID"];
						else
							category.FeaturedPostID =		-1;


                        // fields that are derived at runtime
                        category.TotalThreads =			(int) dr["TotalThreads"];
                        category.TotalSubThreads =			(int) dr["TotalSubThreads"];

                        if(dr["MostRecentPostDate"] != DBNull.Value)
                            category.MostRecentPostDate =	(DateTime) dr["MostRecentPostDate"];
                        else
                            category.MostRecentPostDate = SqlDateTime.MinValue.Value;

                        if(dr["MostRecentSubPostDate"] != DBNull.Value)
                            category.MostRecentSubPostDate =	(DateTime) dr["MostRecentSubPostDate"];
                        else
                            category.MostRecentSubPostDate = SqlDateTime.MinValue.Value;

                        category.Description = dr["Description"] as string;

                        categories.Add(category.CategoryID, category);
                    }

                    dr.Close();
                    connection.Close();
                }

                return categories;
            }
        }

		#region GetWithoutPosts

		public override ArrayList GetEmptyCategories()
		{
			return GetEmptyCategories(GetSectionQuerySQL(ApplicationType.Unknown));
		}

		public override ArrayList GetEmptyCategoriesInSections(int[] sectionIDs)
		{
			return GetEmptyCategories(GetSectionQuerySQLForSections(sectionIDs));
		}

		public override ArrayList GetEmptyCategoriesInGroups(int[] groupIDs)
		{
			return GetEmptyCategories(GetSectionQuerySQLForGroups(groupIDs));
		}

		public override ArrayList GetEmptyCategories(ApplicationType applicationType)
		{
			return GetEmptyCategories(GetSectionQuerySQL(applicationType));
		}

		private ArrayList GetEmptyCategories(string sectionQuerySql)
		{
			using( SqlConnection myConnection = GetSqlConnection() ) 
			{
				SqlCommand myCommand = new SqlCommand(databaseOwner + ".cs_PostCategories_Get_WithoutPosts", myConnection);
				myCommand.CommandType = CommandType.StoredProcedure;
				ArrayList categories = new ArrayList();

				// Set parameters
				//
				myCommand.Parameters.Add("@SectionQuerySQL", SqlDbType.NText).Value = sectionQuerySql;
				myCommand.Parameters.Add(this.SettingsIDParameter());

				// Execute the command
				//
				myConnection.Open();
				using(SqlDataReader dr = myCommand.ExecuteReader(CommandBehavior.CloseConnection))
				{
					// Populate the Hashtable
					while(dr.Read()) 
					{
						// Add categories
						PostCategory category = new PostCategory();

						category.CategoryID =			(int) dr["CategoryID"];
						category.SectionID =			(int) dr["SectionID"];
						category.Name =					(string) dr["Name"];
						category.IsEnabled =			(bool) dr["IsEnabled"];
						category.ParentID =				(int) dr["ParentID"];
						category.Path =					(string) dr["Path"];
						category.DateCreated =				(DateTime) dr["DateCreated"];

						if(dr["FeaturedPostID"] != DBNull.Value)
							category.FeaturedPostID =		(int) dr["FeaturedPostID"];
						else
							category.FeaturedPostID =		-1;

						// fields that are derived at runtime
						category.TotalThreads =			(int) dr["TotalThreads"];
						category.TotalSubThreads =			(int) dr["TotalSubThreads"];

						if(dr["MostRecentPostDate"] != DBNull.Value)
							category.MostRecentPostDate =	(DateTime) dr["MostRecentPostDate"];
						else
							category.MostRecentPostDate = SqlDateTime.MinValue.Value;

						if(dr["MostRecentSubPostDate"] != DBNull.Value)
							category.MostRecentSubPostDate =	(DateTime) dr["MostRecentSubPostDate"];
						else
							category.MostRecentSubPostDate = SqlDateTime.MinValue.Value;

						category.Description = dr["Description"] as string;

						categories.Add(category);
					}

					dr.Close();
					myConnection.Close();
				}

				return categories;
			}
		}		

		#endregion

		#region GetCategories (by name)

		public override ArrayList GetCategories(string name)
		{
			return GetCategories(name, GetSectionQuerySQL(ApplicationType.Unknown));
		}

		public override ArrayList GetCategoriesInSections(string name, int[] sectionIDs)
		{
			return GetCategories(name, GetSectionQuerySQLForSections(sectionIDs));
		}

		public override ArrayList GetCategoriesInGroups(string name, int[] groupIDs)
		{
			return GetCategories(name, GetSectionQuerySQLForGroups(groupIDs));
		}

		public override ArrayList GetCategories(string name, ApplicationType applicationType)
		{
			return GetCategories(name, GetSectionQuerySQL(applicationType));
		}

		private ArrayList GetCategories(string name, string sectionQuerySql)
		{
			using( SqlConnection myConnection = GetSqlConnection() ) 
			{
				SqlCommand myCommand = new SqlCommand(databaseOwner + ".cs_PostCategories_Get_ByName", myConnection);
				myCommand.CommandType = CommandType.StoredProcedure;
				ArrayList categories = new ArrayList();

				// Set parameters
				//
				myCommand.Parameters.Add("@Name", SqlDbType.NVarChar, 256).Value = name;
				myCommand.Parameters.Add("@SectionQuerySQL", SqlDbType.NText).Value = sectionQuerySql;
				myCommand.Parameters.Add(this.SettingsIDParameter());

				// Execute the command
				//
				myConnection.Open();
				using(SqlDataReader dr = myCommand.ExecuteReader(CommandBehavior.CloseConnection))
				{
					// Populate the Hashtable
					while(dr.Read()) 
					{
						// Add categories
						PostCategory category = new PostCategory();

						category.CategoryID =			(int) dr["CategoryID"];
						category.SectionID =			(int) dr["SectionID"];
						category.Name =					(string) dr["Name"];
						category.IsEnabled =			(bool) dr["IsEnabled"];
						category.ParentID =				(int) dr["ParentID"];
						category.Path =					(string) dr["Path"];
						category.DateCreated =				(DateTime) dr["DateCreated"];

						if(dr["FeaturedPostID"] != DBNull.Value)
							category.FeaturedPostID =		(int) dr["FeaturedPostID"];
						else
							category.FeaturedPostID =		-1;

						// fields that are derived at runtime
						category.TotalThreads =			(int) dr["TotalThreads"];
						category.TotalSubThreads =			(int) dr["TotalSubThreads"];

						if(dr["MostRecentPostDate"] != DBNull.Value)
							category.MostRecentPostDate =	(DateTime) dr["MostRecentPostDate"];
						else
							category.MostRecentPostDate = SqlDateTime.MinValue.Value;

						if(dr["MostRecentSubPostDate"] != DBNull.Value)
							category.MostRecentSubPostDate =	(DateTime) dr["MostRecentSubPostDate"];
						else
							category.MostRecentSubPostDate = SqlDateTime.MinValue.Value;

						category.Description = dr["Description"] as string;

						categories.Add(category);
					}

					dr.Close();
					myConnection.Close();
				}

				return categories;
			}
		}		

		#endregion

        #endregion

		#region Blog Links

		public override Int32 CreateLink( Link link ) 
		{
			using(SqlConnection myConnection = GetSqlConnection()) {
				SqlCommand myCommand = new SqlCommand(databaseOwner + ".cs_Link_CreateUpdateDelete", myConnection);
				myCommand.CommandType = CommandType.StoredProcedure;

				// Add the parameters
				myCommand.Parameters.Add("@LinkCategoryID", SqlDbType.Int).Value = link.LinkCategoryID;
				myCommand.Parameters.Add("@Title", SqlDbType.NVarChar, 100).Value = link.Title;
				myCommand.Parameters.Add("@Url", SqlDbType.NVarChar, 255).Value = link.Url;
				myCommand.Parameters.Add("@Rel", SqlDbType.NVarChar, 100).Value = link.Rel;
				myCommand.Parameters.Add("@Description", SqlDbType.NVarChar, 2000).Value = link.Description;
				myCommand.Parameters.Add("@IsEnabled", SqlDbType.Bit).Value = link.IsEnabled;
				myCommand.Parameters.Add("@SortOrder", SqlDbType.Int).Value = link.SortOrder;
                myCommand.Parameters.Add(this.SettingsIDParameter());

				// We want to get the ID back
				myCommand.Parameters.Add("@LinkID", SqlDbType.Int).Direction = ParameterDirection.Output;

				// Execute the command
				myConnection.Open();
				int count = myCommand.ExecuteNonQuery();
				myConnection.Close();

				if(count > 0) {
					try {
						return (int)myCommand.Parameters["@LinkID"].Value;
					}
					catch {
                        
					}
				}

				return -1;
			}
		}

		public override Boolean UpdateLink( Link link ) {
			using(SqlConnection myConnection = GetSqlConnection()) {
				SqlCommand myCommand = new SqlCommand(databaseOwner + ".cs_Link_CreateUpdateDelete", myConnection);
				myCommand.CommandType = CommandType.StoredProcedure;

				// Add the parameters
				myCommand.Parameters.Add("@LinkID", SqlDbType.Int).Value = link.LinkID;
				myCommand.Parameters.Add("@LinkCategoryID", SqlDbType.Int).Value = link.LinkCategoryID;
				myCommand.Parameters.Add("@Title", SqlDbType.NVarChar, 100).Value = link.Title;
				myCommand.Parameters.Add("@Url", SqlDbType.NVarChar, 255).Value = link.Url;
				myCommand.Parameters.Add("@Rel", SqlDbType.NVarChar, 100).Value = link.Rel;
				myCommand.Parameters.Add("@Description", SqlDbType.NVarChar, 2000).Value = link.Description;
				myCommand.Parameters.Add("@IsEnabled", SqlDbType.Bit).Value = link.IsEnabled;
				myCommand.Parameters.Add("@SortOrder", SqlDbType.Int).Value = link.SortOrder;
				myCommand.Parameters.Add(this.SettingsIDParameter());

				// Execute the command
				myConnection.Open();
				int count = myCommand.ExecuteNonQuery();
				myConnection.Close();

				return count > 0;
			}
		}

		public override void DeleteLink( Int32 linkID ) {
			using(SqlConnection myConnection = GetSqlConnection()) {
				SqlCommand myCommand = new SqlCommand(databaseOwner + ".cs_Link_CreateUpdateDelete", myConnection);
				myCommand.CommandType = CommandType.StoredProcedure;

				// Add the parameters
				myCommand.Parameters.Add("@LinkID", SqlDbType.Int).Value = linkID;
				myCommand.Parameters.Add("@DeleteLink", SqlDbType.Bit).Value = true;
				myCommand.Parameters.Add(this.SettingsIDParameter());

				// Execute the command
				myConnection.Open();
				myCommand.ExecuteNonQuery();
				myConnection.Close();
			}
		}

		public override Hashtable GetLinks( Int32 linkCategoryID ) {
			using( SqlConnection connection = GetSqlConnection() ) {
				SqlCommand command = new SqlCommand(databaseOwner + ".cs_Links_Get", connection);
				command.CommandType = CommandType.StoredProcedure;
				Hashtable links = new Hashtable();

				// Add Parameters to SPROC
				//
				command.Parameters.Add("@LinkCategoryID", SqlDbType.Int).Value = linkCategoryID;
                command.Parameters.Add(this.SettingsIDParameter());

				// Execute the command
				connection.Open();
                using(SqlDataReader dr = command.ExecuteReader(CommandBehavior.CloseConnection))
                {

                    // Populate the Hashtable
                    while(dr.Read()) 
                    {
						Link link = PopulateLinkFromIDataReader(dr);
                        links.Add( link.LinkID, link );
                    }

                    dr.Close();
                    connection.Close();
                }

				return links;
			}
		}

		public override void ChangeLinkSortOrder( Int32 linkID, Boolean moveUp ) {
			using( SqlConnection myConnection = GetSqlConnection() ) { 
				SqlCommand myCommand = new SqlCommand(databaseOwner + ".cs_Link_UpdateSortOrder", myConnection); 

				// Mark the Command as a SPROC 
				myCommand.CommandType = CommandType.StoredProcedure; 

				// Pass sproc parameters 
				myCommand.Parameters.Add("@LinkID", SqlDbType.Int).Value = linkID; 
                myCommand.Parameters.Add(this.SettingsIDParameter());
				myCommand.Parameters.Add("@MoveUp", SqlDbType.Bit).Value = moveUp; 

				// Execute the command 
				myConnection.Open(); 
				myCommand.ExecuteNonQuery(); 

				myConnection.Close(); 
			}
		}


		public override Int32 CreateLinkCategory( LinkCategory linkCategory ) {
			using(SqlConnection myConnection = GetSqlConnection()) {
				SqlCommand myCommand = new SqlCommand(databaseOwner + ".cs_LinkCategory_CreateUpdateDelete", myConnection);
				myCommand.CommandType = CommandType.StoredProcedure;

				// Add the parameters
				myCommand.Parameters.Add("@SectionID", SqlDbType.Int).Value = linkCategory.SectionID;
				myCommand.Parameters.Add("@Name", SqlDbType.NVarChar, 256).Value = linkCategory.Name;
				myCommand.Parameters.Add("@Description", SqlDbType.NVarChar, 2000).Value = linkCategory.Description;
				myCommand.Parameters.Add("@IsEnabled", SqlDbType.Bit).Value = linkCategory.IsEnabled;
				myCommand.Parameters.Add("@SortOrder", SqlDbType.Int).Value = linkCategory.SortOrder;
                myCommand.Parameters.Add(this.SettingsIDParameter());

				// We want to get the ID back
				myCommand.Parameters.Add("@LinkCategoryID", SqlDbType.Int).Direction = ParameterDirection.Output;

				// Execute the command
				myConnection.Open();
				int count = myCommand.ExecuteNonQuery();
				myConnection.Close();

				if(count > 0) {
					try {
						return (int)myCommand.Parameters["@LinkCategoryID"].Value;
					}
					catch {
                        
					}
				}

				return -1;
			}
		}

		public override Boolean UpdateLinkCategory( LinkCategory linkCategory ) {
			using(SqlConnection myConnection = GetSqlConnection()) {
				SqlCommand myCommand = new SqlCommand(databaseOwner + ".cs_LinkCategory_CreateUpdateDelete", myConnection);
				myCommand.CommandType = CommandType.StoredProcedure;

				// Add the parameters
				myCommand.Parameters.Add("@LinkCategoryID", SqlDbType.Int).Value = linkCategory.LinkCategoryID;
				myCommand.Parameters.Add("@SectionID", SqlDbType.Int).Value = linkCategory.SectionID;
				myCommand.Parameters.Add("@Name", SqlDbType.NVarChar, 256).Value = linkCategory.Name;
				myCommand.Parameters.Add("@Description", SqlDbType.NVarChar, 2000).Value = linkCategory.Description;
				myCommand.Parameters.Add("@IsEnabled", SqlDbType.Bit).Value = linkCategory.IsEnabled;
				myCommand.Parameters.Add("@SortOrder", SqlDbType.Int).Value = linkCategory.SortOrder;
				myCommand.Parameters.Add(this.SettingsIDParameter());

				// Execute the command
				myConnection.Open();
				int count = myCommand.ExecuteNonQuery();
				myConnection.Close();

				return count > 0;
			}
		}

		public override void DeleteLinkCategory( Int32 linkCategoryID, Int32 forumID ) {
			using(SqlConnection myConnection = GetSqlConnection()) {
				SqlCommand myCommand = new SqlCommand(databaseOwner + ".cs_LinkCategory_CreateUpdateDelete", myConnection);
				myCommand.CommandType = CommandType.StoredProcedure;

				// Add the parameters
				myCommand.Parameters.Add("@LinkCategoryID", SqlDbType.Int).Value = linkCategoryID;
                myCommand.Parameters.Add("@SectionID", SqlDbType.Int).Value = forumID;
				myCommand.Parameters.Add("@DeleteLinkCategory", SqlDbType.Bit).Value = true;
				myCommand.Parameters.Add(this.SettingsIDParameter());

				// Execute the command
				myConnection.Open();
				myCommand.ExecuteNonQuery();
				myConnection.Close();
			}
		}

		public override Hashtable GetLinkCategories( Int32 sectionID, Boolean preloadLinks ) {
			using( SqlConnection connection = GetSqlConnection() ) {
				SqlCommand command = new SqlCommand(databaseOwner + ".cs_LinkCategories_Get", connection);
				command.CommandType = CommandType.StoredProcedure;
				Hashtable linkCategories = new Hashtable();

				// Add Parameters to SPROC
				command.Parameters.Add("@SectionID", SqlDbType.Int).Value = sectionID;
                command.Parameters.Add(this.SettingsIDParameter());
				command.Parameters.Add("@PreLoadLinks", SqlDbType.Bit).Value = preloadLinks;

				// Execute the command
				connection.Open();
                using(SqlDataReader dr = command.ExecuteReader(CommandBehavior.CloseConnection))
                {

                    // Populate the Hashtable
                    while(dr.Read()) 
                    {
                        // Add links
                        LinkCategory linkCategory = new LinkCategory();
                        linkCategory.LinkCategoryID = (Int32)dr[ "LinkCategoryID" ];
                        linkCategory.SectionID = (Int32)dr[ "SectionID" ];
                        linkCategory.Name = (String)dr[ "Name" ];
                        if ( !dr.IsDBNull( dr.GetOrdinal( "Description" ) ) ) 
                        {
                            linkCategory.Description = (String)dr[ "Description" ];
                        }
                        linkCategory.IsEnabled = (Boolean)dr[ "IsEnabled" ];
                        linkCategory.SortOrder = (Int32)dr[ "SortOrder" ];

                        linkCategories.Add( linkCategory.LinkCategoryID, linkCategory );
                    }

					// Get the links if requested
					if (preloadLinks)
					{
						ArrayList links = new ArrayList();

						dr.NextResult();
						while(dr.Read()) 
						{
							links.Add(PopulateLinkFromIDataReader(dr));
						}
                    dr.Close();

						foreach (LinkCategory cat in linkCategories.Values)
						{
							foreach (Link link in links)
							{
								if (link.LinkCategoryID == cat.LinkCategoryID)
									cat.Links.Add(link);
							}
						}

					}
					else
					{
						dr.Close();
					}

                    connection.Close();
                }

				return linkCategories;
			}
		}

		public override void ChangeLinkCategorySortOrder( Int32 linkCategoryID, Boolean moveUp ) {
			using( SqlConnection myConnection = GetSqlConnection() ) { 
				SqlCommand myCommand = new SqlCommand(databaseOwner + ".cs_LinkCategory_UpdateSortOrder", myConnection); 

				// Mark the Command as a SPROC 
				myCommand.CommandType = CommandType.StoredProcedure; 

				// Pass sproc parameters 
				myCommand.Parameters.Add("@LinkCategoryID", SqlDbType.Int).Value = linkCategoryID; 
				myCommand.Parameters.Add("@MoveUp", SqlDbType.Bit).Value = moveUp; 
                myCommand.Parameters.Add(this.SettingsIDParameter());

				// Execute the command 
				myConnection.Open(); 
				myCommand.ExecuteNonQuery(); 

				myConnection.Close(); 
			}
		}

		#endregion

		#region Blog Feedback

		
		public override ArrayList GetFeedback( Int32 forumID ) {
			Int32 totalRecords;
			return GetFeedback( forumID, 0, 0, out totalRecords );
		}

		public override ArrayList GetFeedback( Int32 forumID, Int32 pageIndex, Int32 pageSize, out Int32 totalRecords ) {
			ArrayList feedback = new ArrayList();

			using ( SqlConnection cn = GetSqlConnection() )
			using ( SqlCommand cmd = new SqlCommand( databaseOwner + ".cs_weblog_Feedback_Get", cn ) ) {
				cmd.CommandType = CommandType.StoredProcedure;

				cmd.Parameters.Add("@SectionID", SqlDbType.Int).Value = forumID;
                cmd.Parameters.Add(this.SettingsIDParameter());
				cmd.Parameters.Add("@TotalRecords", SqlDbType.Int ).Direction = ParameterDirection.Output;
				if ( pageSize > 0 ) {
					cmd.Parameters.Add( "@UsePaging", SqlDbType.Bit ).Value = true;
					cmd.Parameters.Add( "@PageSize", SqlDbType.Int ).Value = SQLHelper.GetSafeSqlInt(pageSize) ;
					cmd.Parameters.Add( "@PageIndex", SqlDbType.Int ).Value = pageIndex;
				}
				
				cn.Open();
                using(SqlDataReader dr = cmd.ExecuteReader())
                {

                    // PostID, PostAuthor, Subject, PostDate, IsApproved, PostName, TitleUrl
                    while ( dr.Read() ) 
                    {
                    	WeblogPost item = new WeblogPost();
					
                        item.PostID = (Int32)dr[ "PostID" ];
                        item.Username = (String)dr[ "PostAuthor" ];
                        item.Subject = (String)dr[ "Subject" ];
                        item.IsApproved = (Boolean)dr[ "IsApproved" ];
                        item.TitleUrl = dr[ "TitleUrl" ] as String;
                        item.PostDate = (DateTime)dr[ "PostDate" ];
                        item.Body = dr[ "Body" ] as String;

                        feedback.Add( item );
                    }

                    dr.Close();
                }

				totalRecords = (Int32)cmd.Parameters[ "@TotalRecords" ].Value;

				cn.Close();

			}

			return feedback;
		}

		#endregion

        #region #### Vote ####
        /****************************************************************
        // Vote
        //
        /// <summary>
        /// Votes for a poll
        /// </summary>
        //
        ****************************************************************/
        public override void Vote(int postID, int userID, string selection) {
            // Create Instance of Connection and Command Object
			using( SqlConnection myConnection = GetSqlConnection() ) {
				SqlCommand myCommand = new SqlCommand(databaseOwner + ".cs_Vote_Create", myConnection);

				// Mark the Command as a SPROC
				myCommand.CommandType = CommandType.StoredProcedure;

				// Add Parameters to SPROC
				myCommand.Parameters.Add("@PostID", SqlDbType.Int).Value = postID;
				myCommand.Parameters.Add("@UserID", SqlDbType.Int).Value = userID;
				myCommand.Parameters.Add("@Vote", SqlDbType.NVarChar, 2).Value = selection;
                myCommand.Parameters.Add(SettingsIDParameter());

				// Execute the command
				myConnection.Open();
				myCommand.ExecuteNonQuery();
				myConnection.Close();
			}
        }
        
        /****************************************************************
        // GetVoteResults
        //
        /// <summary>
        /// Returns a collection of threads that the user has recently partipated in.
        /// </summary>
        //
        ****************************************************************/
        public override PollSummary GetPoll(PollSummary summary) {

            // Create Instance of Connection and Command Object
			using( SqlConnection myConnection = GetSqlConnection() ) {
				SqlCommand myCommand = new SqlCommand(databaseOwner + ".cs_Vote_GetSummary", myConnection);

				// Mark the Command as a SPROC
				myCommand.CommandType = CommandType.StoredProcedure;

				// Add Parameters to SPROC
				myCommand.Parameters.Add("@PostID", SqlDbType.Int).Value = summary.PostID;
                myCommand.Parameters.Add(SettingsIDParameter());

				// Execute the command
				myConnection.Open();
                using(SqlDataReader dr = myCommand.ExecuteReader(CommandBehavior.CloseConnection))
                {

                    // Read the values
                    //
                    while (dr.Read()) 
                    {
						PollItem item = summary.GetPollItemByAnswerID((string) dr["Vote"]);
                        if (item != null)
                            item.Total = (int) dr["VoteCount"];
                    }
            
                    if (dr.NextResult()) 
                    {
                        while (dr.Read()) 
                        {
                            summary.Voters[(int) dr["UserID"]] = (string) dr["Vote"];
                        }
                    }

                    dr.Close();
                }
            
				// Close the conneciton
				myConnection.Close();

				return summary;
			}
        }

        #endregion

		#region Tokens

		public override ArrayList GetTokens(int sectionid)
		{
			ArrayList list = new ArrayList();
			using(SqlConnection myConnection = GetSqlConnection()) 
			{
				using(SqlCommand myCommand = new SqlCommand(databaseOwner + ".cs_SectionTokens_Get", myConnection))
				{
					myCommand.CommandType = CommandType.StoredProcedure;

					myCommand.Parameters.Add("@sectionid", SqlDbType.Int).Value = sectionid;
					myCommand.Parameters.Add(this.SettingsIDParameter());

					myConnection.Open();
					using(SqlDataReader reader = myCommand.ExecuteReader(CommandBehavior.CloseConnection))
					{
						
						TextToken token = null;
						while(reader.Read())
						{
							token = new TextToken();
							token.Link = reader["link"] as string;
							token.Text = reader["Text"] as string;
							token.Token = reader["Token"] as string;
							token.TokenID = (int)reader["TokenID"];
							token.SectionID = sectionid;

							list.Add(token);
						}
					}
				}
				myConnection.Close();
			}


			return list;
		}

		public override int AddToken(TextToken token)
		{
			using(SqlConnection myConnection = GetSqlConnection()) 
			{
				using(SqlCommand myCommand = new SqlCommand(databaseOwner + ".cs_SectionTokens_Add", myConnection))
				{
					myCommand.CommandType = CommandType.StoredProcedure;

					SqlParameter tokenid = new SqlParameter("@TokenID", SqlDbType.Int,4);
					tokenid.Direction = ParameterDirection.Output;
					myCommand.Parameters.Add(tokenid);
					myCommand.Parameters.Add("@token", SqlDbType.NVarChar, 50).Value = token.Token;
					myCommand.Parameters.Add("@sectionid", SqlDbType.Int).Value = token.SectionID;
					myCommand.Parameters.Add("@link", SqlDbType.NVarChar, 255).Value = token.Link;
					myCommand.Parameters.Add("@text", SqlDbType.NVarChar, 500).Value = token.Text;
					myCommand.Parameters.Add(this.SettingsIDParameter());
					
					myConnection.Open();
					myCommand.ExecuteNonQuery();
					myConnection.Close();
					return (int)myCommand.Parameters["@TokenID"].Value;
				}
				
			}
			
		}

		public override void UpdateToken(TextToken token)
		{
			using(SqlConnection myConnection = GetSqlConnection()) 
			{
				using(SqlCommand myCommand = new SqlCommand(databaseOwner + ".cs_SectionTokens_Update", myConnection))
				{
					myCommand.CommandType = CommandType.StoredProcedure;

					myCommand.Parameters.Add("@token", SqlDbType.NVarChar, 50).Value = token.Token;
					myCommand.Parameters.Add("@tokenid", SqlDbType.Int).Value = token.TokenID;
					myCommand.Parameters.Add("@link", SqlDbType.NVarChar, 255).Value = token.Link;
					myCommand.Parameters.Add("@text", SqlDbType.NVarChar, 500).Value = token.Text;
					myCommand.Parameters.Add("@sectionid", SqlDbType.Int).Value = token.SectionID;
					myCommand.Parameters.Add(this.SettingsIDParameter());

					myConnection.Open();
					myCommand.ExecuteNonQuery();
					myConnection.Close();

				}
			}
		}

		public override void DeleteToken(TextToken token)
		{
			using(SqlConnection myConnection = GetSqlConnection()) 
			{
				using(SqlCommand myCommand = new SqlCommand(databaseOwner + ".cs_SectionTokens_Delete", myConnection))
				{
					myCommand.CommandType = CommandType.StoredProcedure;

					myCommand.Parameters.Add("@tokenid", SqlDbType.Int).Value = token.TokenID;
					myCommand.Parameters.Add("@sectionid", SqlDbType.Int).Value = token.SectionID;
					myCommand.Parameters.Add(this.SettingsIDParameter());

					myConnection.Open();
					myCommand.ExecuteNonQuery();
					myConnection.Close();
				}
			}
			
		}




		#endregion

		#region Tags

		#region Tag Helpers

		public string GetSectionQuerySQL(ApplicationType applicationType)
		{
			StringBuilder query = new StringBuilder();
			ArrayList list = new ArrayList();

			query.Append("SELECT SectionID from cs_Sections WHERE SectionID IN (");

			if (applicationType == ApplicationType.Unknown)
			{
				foreach (CSApplicationData app in ApplicationSet.Applications)
				{
					if (app.Enabled && app.SupportsTags)
						list.AddRange(app.Sections());
				}
			}
			else
			{
				CSApplicationData app = ApplicationSet.Applications[applicationType];

				if (app.Enabled && app.SupportsTags)
					list.AddRange(app.Sections());
			}

			if (list.Count > 0)
			{
				for(int i = 0; i < list.Count; i++)
				{
					query.Append(((Section) list[i]).SectionID.ToString());
					if ((i+1) < list.Count) 
					{
						query.Append(",");
					} 
				}
			}
			else
				query.Append("null");

			query.Append(") and IsActive = 1");

			return query.ToString();
		}

		public string GetSectionQuerySQLForSection(int sectionID)
		{
			return GetSectionQuerySQLForSections(new int[] { sectionID });
		}

		public string GetSectionQuerySQLForSections(int[] sectionIDs)
		{
			StringBuilder query = new StringBuilder();

			query.Append("SELECT SectionID from cs_Sections WHERE SectionID IN (");

			bool isFirst = true;
			foreach (int id in sectionIDs)
			{
				if (isFirst)
					isFirst = false;
				else
					query.Append(",");

				query.Append(id);
			}

			query.Append(") and IsActive = 1");

			return query.ToString();
		}

		public string GetSectionQuerySQLForGroup(int groupID)
		{
			return GetSectionQuerySQLForGroups(new int[] { groupID });
		}

		public string GetSectionQuerySQLForGroups(int[] groupIDs)
		{
			StringBuilder query = new StringBuilder();
			ArrayList list = new ArrayList();

			query.Append("SELECT SectionID from cs_Sections WHERE GroupID in (");
			
			bool isFirst = true;
			foreach (int id in groupIDs)
			{
				if (isFirst)
					isFirst = false;
				else
					query.Append(",");

				query.Append(id);
			}

			query.Append(") AND SectionID IN (");

			foreach (CSApplicationData app in ApplicationSet.Applications)
			{
				if (app.Enabled && app.SupportsTags)
					list.AddRange(app.Sections());
			}

			if (list.Count > 0)
			{
				for(int i = 0; i < list.Count; i++)
				{
					query.Append(((Section) list[i]).SectionID.ToString());
					if ((i+1) < list.Count) 
					{
						query.Append(",");
					} 
				}
			}
			else
				query.Append("null");

			query.Append(") and IsActive = 1");

			return query.ToString();
		}

		#endregion

		#region GetTags

		public override ArrayList GetTags(string[] tags)
		{
			return GetTags(GetSectionQuerySQL(ApplicationType.Unknown), ConvertCategoriesToXML(tags));
		}

		public override ArrayList GetTagsBySections(int[] sectionIDs, string[] tags)
		{
			return GetTags(GetSectionQuerySQLForSections(sectionIDs), ConvertCategoriesToXML(tags));
		}

		public override ArrayList GetTagsByGroups(int[] groupIDs, string[] tags)
		{
			return GetTags(GetSectionQuerySQLForGroups(groupIDs), ConvertCategoriesToXML(tags));
		}

		public override ArrayList GetTags(ApplicationType applicationType, string[] tags)
		{
			return GetTags(GetSectionQuerySQL(applicationType), ConvertCategoriesToXML(tags));
		}

		public override ArrayList GetTags()
		{
			return GetTags(GetSectionQuerySQL(ApplicationType.Unknown), null);
		}

		public override ArrayList GetTagsBySections(int[] sectionIDs)
		{
			return GetTags(GetSectionQuerySQLForSections(sectionIDs), null);
		}

		public override ArrayList GetTagsByGroups(int[] groupIDs)
		{
			return GetTags(GetSectionQuerySQLForGroups(groupIDs), null);
		}

		public override ArrayList GetTags(ApplicationType applicationType)
		{
			return GetTags(GetSectionQuerySQL(applicationType), null);
		}

		private ArrayList GetTags(string sectionQuerySql, object tagXml)
		{
			using( SqlConnection myConnection = GetSqlConnection() ) 
			{
				SqlCommand myCommand = new SqlCommand(databaseOwner + ".cs_Tags_Get", myConnection);
				myCommand.CommandType = CommandType.StoredProcedure;
				ArrayList newTags = new ArrayList();

				// Set parameters
				//
				myCommand.Parameters.Add("@SectionQuerySQL", SqlDbType.NText).Value = sectionQuerySql;
				myCommand.Parameters.Add("@CategoryList", SqlDbType.NVarChar, 4000).Value = tagXml;
				myCommand.Parameters.Add(this.SettingsIDParameter());

				// Execute the command
				//
				myConnection.Open();
				SqlDataReader dr = myCommand.ExecuteReader();

				// Get the results
				//
				while(dr.Read())
					newTags.Add(PopulateTagFromIDataReader(dr));

				dr.Close();
				myConnection.Close();

				return newTags;
			}
		}

		#endregion

		#endregion

		#region UserInvitations

		public override ArrayList GetUserInvitationsBeforeDate(DateTime dateInvited)
		{
			using( SqlConnection myConnection = GetSqlConnection() ) 
			{
				SqlCommand myCommand = new SqlCommand(databaseOwner + ".cs_UserInvitations_GetBeforeDate", myConnection);
				myCommand.CommandType = CommandType.StoredProcedure;

				// Mark the Command as a SPROC

				// Add Parameters to SPROC
				myCommand.Parameters.Add("@DateInvited", SqlDbType.DateTime).Value = dateInvited;

				// Execute the command
				myConnection.Open();
				ArrayList invitations = new ArrayList();
				using(SqlDataReader dr = myCommand.ExecuteReader(CommandBehavior.CloseConnection))
				{
					while(dr.Read())
					{
						UserInvitation ui = new UserInvitation();
						ui = PopulateUserInvitationFromIReader(dr);
						invitations.Add(ui);
					}

					dr.Close();
				}
				myConnection.Close();

				return invitations;
			}        
		}

		public override ArrayList GetUserInvitationsForUser(int userID)
		{
			using( SqlConnection myConnection = GetSqlConnection() ) 
			{
				SqlCommand myCommand = new SqlCommand(databaseOwner + ".cs_UserInvitations_GetByUser", myConnection);
				myCommand.CommandType = CommandType.StoredProcedure;

				// Mark the Command as a SPROC

				// Add Parameters to SPROC
				myCommand.Parameters.Add("@UserID", SqlDbType.Int).Value = userID;

				// Execute the command
				myConnection.Open();
				ArrayList invitations = new ArrayList();
				using(SqlDataReader dr = myCommand.ExecuteReader(CommandBehavior.CloseConnection))
				{
					while(dr.Read())
					{
						UserInvitation ui = new UserInvitation();
						ui = PopulateUserInvitationFromIReader(dr);
						invitations.Add(ui);
					}

					dr.Close();
				}
				myConnection.Close();

				return invitations;
			}    
		}

		public override UserInvitation GetUserInvitation(Guid invitationKey)
		{
			using( SqlConnection myConnection = GetSqlConnection() ) 
			{
				SqlCommand myCommand = new SqlCommand(databaseOwner + ".cs_UserInvitation_Get", myConnection);
				myCommand.CommandType = CommandType.StoredProcedure;

				// Mark the Command as a SPROC

				// Add Parameters to SPROC
				myCommand.Parameters.Add("@InvitationKey", SqlDbType.UniqueIdentifier).Value = invitationKey;

				// Execute the command
				myConnection.Open();
				UserInvitation ui = null;
				using(SqlDataReader dr = myCommand.ExecuteReader(CommandBehavior.CloseConnection))
				{
					if(dr.Read())
					{
						ui = PopulateUserInvitationFromIReader(dr);
					}
					dr.Close();
				}
				myConnection.Close();

				return ui;
			}        
		}

		public override void CreateUserInvitation(UserInvitation invite)
		{
			using(SqlConnection myConnection = GetSqlConnection()) 
			{
				SqlCommand myCommand = new SqlCommand(databaseOwner + ".cs_UserInvitation_Create", myConnection);
				myCommand.CommandType = CommandType.StoredProcedure;

				invite.DateInvited = DateTime.Now;

				// Add the parameters
				myCommand.Parameters.Add("@UserID", SqlDbType.Int).Value = invite.UserID;
				myCommand.Parameters.Add("@Email", SqlDbType.NVarChar, 255).Value = invite.Email;
				myCommand.Parameters.Add("@DateInvited", SqlDbType.NVarChar, 2000).Value = invite.DateInvited;

				SerializerData data = invite.GetSerializerData();

				myCommand.Parameters.Add("@PropertyNames", SqlDbType.NText).Value = data.Keys;
				myCommand.Parameters.Add("@PropertyValues", SqlDbType.NText).Value = data.Values;

				// We want to get the ID back
				myCommand.Parameters.Add("@InvitationKey", SqlDbType.UniqueIdentifier).Direction = ParameterDirection.Output;

				// Execute the command
				myConnection.Open();
				myCommand.ExecuteNonQuery();
				myConnection.Close();

				invite.InvitationKey = (Guid) myCommand.Parameters["@InvitationKey"].Value;
			}
		}

		public override void DeleteUserInvitation(Guid invitationKey)
		{
			using(SqlConnection myConnection = GetSqlConnection()) 
			{
				SqlCommand myCommand = new SqlCommand(databaseOwner + ".cs_UserInvitation_Delete", myConnection);
				myCommand.CommandType = CommandType.StoredProcedure;

				// Add the parameters
				myCommand.Parameters.Add("@InvitationKey", SqlDbType.UniqueIdentifier).Value = invitationKey;

				// Execute the command
				myConnection.Open();
				myCommand.ExecuteNonQuery();
				myConnection.Close();
			}
		}

		public override void ExpireUserInvitations(DateTime expirationDate)
		{
			using(SqlConnection myConnection = GetSqlConnection()) 
			{
				SqlCommand myCommand = new SqlCommand(databaseOwner + ".cs_UserInvitations_Expire", myConnection);
				myCommand.CommandType = CommandType.StoredProcedure;

				// Add the parameters
				myCommand.Parameters.Add("@DateInvited", SqlDbType.DateTime).Value = expirationDate;

				// Execute the command
				myConnection.Open();
				myCommand.ExecuteNonQuery();
				myConnection.Close();
			}
		}

		#endregion

		public override InkData CreateUpdateDeleteInkData(InkData data, DataProviderAction action)
		{
			string sql = null;
			if(action == DataProviderAction.Create)
			{
				sql = "cs_InkData_Add";
			}
			else if(action == DataProviderAction.Update)
			{
				sql = "cs_InkData_Update";
			}
			else
			{
				sql = "cs_InkData_Delete";
			}

			using(SqlConnection conn = GetSqlConnection())
			{
				using(SqlCommand command = new SqlCommand(sql,conn))
				{
					command.CommandType = CommandType.StoredProcedure;

					command.Parameters.Add(SettingsIDParameter());
					command.Parameters.Add("@SectionID", SqlDbType.Int,4).Value = data.SectionID;
					command.Parameters.Add("@InkID", SqlDbType.Int,4);
					if(action != DataProviderAction.Delete)
					{
						command.Parameters.Add("@Ink", SqlDbType.NText).Value = data.InkString;
						command.Parameters.Add("@DateUpdated", SqlDbType.DateTime).Value = data.DateUpdated;
					}

					if(action == DataProviderAction.Create)
					{
						command.Parameters["@InkID"].Direction = ParameterDirection.Output;
						command.Parameters.Add("@UserID", SqlDbType.Int,4).Value = data.UserID;
						command.Parameters.Add("@ApplicationType", SqlDbType.SmallInt).Value = data.ApplicationType;
						
					}
					else
					{
						command.Parameters["@InkID"].Value = data.InkID;
					}

					conn.Open();
					command.ExecuteNonQuery();
					conn.Close();
					if(action == DataProviderAction.Create)
					{
						data.InkID = (int)command.Parameters["@InkID"].Value;
					}
				}

				return data;
			}
		}

		public override InkData GetInkData(int inkID)
		{
			using(SqlConnection conn = GetSqlConnection())
			{
				using(SqlCommand command = new SqlCommand("cs_InkData_Get",conn))
				{
					command.CommandType = CommandType.StoredProcedure;

					command.Parameters.Add(SettingsIDParameter());
					command.Parameters.Add("@InkID", SqlDbType.Int,4).Value = inkID;

					conn.Open();
					using(SqlDataReader reader = command.ExecuteReader(CommandBehavior.CloseConnection|CommandBehavior.SingleRow))
					{
						InkData data = null;
						if(reader.Read())
						{
							data = new InkData();	
							data.ApplicationType = (ApplicationType) (Int16)reader["ApplicationType"];
							data.DateUpdated = (DateTime)reader["DateUpdated"];
							data.InkID = (int)reader["InkID"];
							data.UserID = (int)reader["UserID"];
							data.InkString = reader["Ink"] as string;
							data.SectionID = (int)reader["SectionID"];

						}

						reader.Close();
						conn.Close();
						return data;
					}

				}
			}
		}



        /****************************************************************
        // ToggleOptions
        //
        /// <summary>
        /// Allows use to change various settings without updating the profile directly
        /// </summary>
        //
        ****************************************************************/
        public override void ToggleOptions(string username, bool hideReadThreads, ViewOptions viewOptions) 
		{
			using( SqlConnection myConnection = GetSqlConnection() ) {
				SqlCommand myCommand = new SqlCommand(databaseOwner + ".forums_ToggleOptions", myConnection);

				// Mark the Command as a SPROC
				myCommand.CommandType = CommandType.StoredProcedure;

				// Pass sproc parameters
				myCommand.Parameters.Add("@UserName", SqlDbType.NVarChar, 50).Value = username;
				myCommand.Parameters.Add("@HideReadThreads", SqlDbType.Bit).Value = hideReadThreads;

				if (ViewOptions.NotSet == viewOptions)
					myCommand.Parameters.Add("@FlatView", SqlDbType.Bit).Value = DBNull.Value;
				else if (ViewOptions.Threaded == viewOptions)
					myCommand.Parameters.Add("@FlatView", SqlDbType.Bit).Value = false;
				else
					myCommand.Parameters.Add("@FlatView", SqlDbType.Bit).Value = true;

				// Execute the command
				myConnection.Open();
				myCommand.ExecuteNonQuery();

				myConnection.Close();
			}
        }

        public override void ReOrderGroup(int GroupID, int Index)
        {
            using( SqlConnection myConnection = GetSqlConnection() ) 
            {
                SqlCommand myCommand = new SqlCommand(databaseOwner + ".cs_Groups_ReOrder", myConnection);

                // Mark the Command as a SPROC
                myCommand.CommandType = CommandType.StoredProcedure;

                // Pass sproc parameters
                myCommand.Parameters.Add("@GroupID", SqlDbType.Int).Value = GroupID;
                myCommand.Parameters.Add("@Index", SqlDbType.Int).Value = Index;
                myCommand.Parameters.Add(this.SettingsIDParameter());


                // Execute the command
                myConnection.Open();
                myCommand.ExecuteNonQuery();

                myConnection.Close();
            }
        }


        /****************************************************************
        // UpdateMessageTemplate
        //
        /// <summary>
        /// update the message in the database
        /// </summary>
        //
        ****************************************************************/
        public override void CreateUpdateDeleteMessage (Message message, DataProviderAction action) {

            // return all of the forums and their total and daily posts
            // Create Instance of Connection and Command Object
			using( SqlConnection myConnection = GetSqlConnection() ) {
				SqlCommand myCommand = new SqlCommand(databaseOwner + ".cs_Message_CreateUpdateDelete", myConnection);

				// Mark the Command as a SPROC
				myCommand.CommandType = CommandType.StoredProcedure;

				// Pass sproc parameters
				myCommand.Parameters.Add("@MessageID", SqlDbType.Int).Value         = message.MessageID;
				myCommand.Parameters.Add("@Title", SqlDbType.NVarChar, 1024).Value  = message.Title;
				myCommand.Parameters.Add("@Body", SqlDbType.NText).Value            = message.Body;
				myCommand.Parameters.Add("@Action", SqlDbType.Int).Value            = action;
                myCommand.Parameters.Add(this.SettingsIDParameter());

				// Execute the command
				myConnection.Open();
				myCommand.ExecuteNonQuery();
				myConnection.Close();
			}
        }


        /****************************************************************
        // GetMessages
        //
        /// <summary>
        /// Returns a collection of ForumMessages
        /// </summary>
        //
        ****************************************************************/
        public override ArrayList GetMessages(int messageID) {

            // return all of the forums and their total and daily posts
            // Create Instance of Connection and Command Object
			using( SqlConnection myConnection = GetSqlConnection() ) {
				SqlCommand myCommand = new SqlCommand(databaseOwner + ".cs_forums_GetForumMessages", myConnection);
				SqlDataReader reader;
				ArrayList messages = new ArrayList();
				//            ForumMessage message;

				// Mark the Command as a SPROC
				myCommand.CommandType = CommandType.StoredProcedure;

				myCommand.Parameters.Add("@MessageID", SqlDbType.Int).Value = messageID;
                myCommand.Parameters.Add(this.SettingsIDParameter());

				// Execute the command
				myConnection.Open();
                using(reader = myCommand.ExecuteReader(CommandBehavior.CloseConnection))
                {

                    while (reader.Read()) 
                    {
                        // TODO Terry Denham, ForumMessages were moved to xml file instead of db. This will need to be updated at some point.
                        /*                message = new ForumMessage();

                                        message.MessageID = Convert.ToInt32(reader["MessageID"]);
                                        message.Title = (string) reader["Title"];
                                        message.Body = (string) reader["Body"];

                                        messages.Add(message);
                        */
                    }
                }

				myConnection.Close();

				return messages;
			}
        }



        /****************************************************************
        // GetUserIDByEmail
        //
        /// <summary>
        /// Returns the username given an email address
        /// </summary>
        //
        ****************************************************************/
        public override int GetUserIDByEmail(string emailAddress) {
            // return all of the forums and their total and daily posts
            // Create Instance of Connection and Command Object
			using( SqlConnection myConnection = GetSqlConnection() ) {
				SqlCommand myCommand = new SqlCommand(databaseOwner + ".cs_User_GetByEmail", myConnection);
				SqlDataReader reader;
				int userID = 0;

				// Mark the Command as a SPROC
				myCommand.CommandType = CommandType.StoredProcedure;

				// Pass sproc parameters
				myCommand.Parameters.Add("@Email", SqlDbType.NVarChar, 64).Value = emailAddress;
                myCommand.Parameters.Add(this.SettingsIDParameter());

				// Execute the command
				myConnection.Open();
                using(reader = myCommand.ExecuteReader(CommandBehavior.CloseConnection))
                {

                    while (reader.Read())
                        userID = (int) reader["cs_UserID"];

                    reader.Close();
                }
				myConnection.Close();

				return userID;
			}
        }


		/****************************************************************
		// GetUserIDByEmail
		//
		/// <summary>
		/// Returns the userid given an application-specific user token (e.g., Passport user ID)
		/// </summary>
		//
		****************************************************************/
		public override int GetUserIDByAppUserToken(string appUserToken) 
		{
			int userID = 0;

			// Create Instance of Connection and Command Object
			using (SqlConnection myConnection = GetSqlConnection())
			{
				SqlCommand myCommand = new SqlCommand(databaseOwner + ".cs_GetUserIDByAppToken", myConnection);

				// Mark the Command as a SPROC
				myCommand.CommandType = CommandType.StoredProcedure;

				// Pass sproc parameters
				myCommand.Parameters.Add("@AppUserToken", SqlDbType.VarChar, 128).Value = appUserToken;
                myCommand.Parameters.Add(SettingsIDParameter());

				// Execute the command
				myConnection.Open();
				object res = myCommand.ExecuteScalar();
                myConnection.Close();

				// this method is not expected to throw any exceptions
				// return 0 if the user is not found
				if (res != DBNull.Value)
					userID = Convert.ToInt32(res);
			}

			return userID;
		}

        /****************************************************************
        // UserChangePasswordAnswer
        //
        /// <summary>
        /// Change the password answer for the user.
        /// </summary>
        //
        ****************************************************************/
        public override void UserChangePasswordAnswer (int userID, string newQuestion, string newAnswer)
        {

            using( SqlConnection myConnection = GetSqlConnection() ) 
            {
                SqlCommand myCommand = new SqlCommand(databaseOwner + ".cs_User_PasswordAnswer_Change", myConnection);

                // Mark the Command as a SPROC
                myCommand.CommandType = CommandType.StoredProcedure;

                // Pass sproc parameters
                myCommand.Parameters.Add("@UserID", SqlDbType.Int).Value = userID;
                myCommand.Parameters.Add("@PasswordQuestion", SqlDbType.NVarChar, 256).Value = newQuestion;
                myCommand.Parameters.Add("@PasswordAnswer", SqlDbType.NVarChar, 256).Value = newAnswer;
                myCommand.Parameters.Add(this.SettingsIDParameter());

                // Execute the command
                myConnection.Open();
                myCommand.ExecuteNonQuery();
                myConnection.Close();
            }
        }

        
        /****************************************************************
        // GetModeratedPostsByForumId
        //
        /// <summary>
        /// Returns all the posts in a given forum that require moderation.
        /// </summary>
        //
        ****************************************************************/
        private  PostSet GetModeratedPostsByForumId(int forumId) 
        {
            // return all of the forums and their total and daily posts
            // Create Instance of Connection and Command Object
			using( SqlConnection myConnection = GetSqlConnection() ) {
				SqlCommand myCommand = new SqlCommand(databaseOwner + ".forums_GetModeratedPostsByForumId", myConnection);

				// Mark the Command as a SPROC
				myCommand.CommandType = CommandType.StoredProcedure;

				// Pass sproc parameters
				myCommand.Parameters.Add("@UserName", SqlDbType.Int).Value = forumId;

                PostSet postSet = new PostSet();

				// Execute the command
				myConnection.Open();
                using(SqlDataReader dr = myCommand.ExecuteReader(CommandBehavior.CloseConnection))
                {
                    while (dr.Read())
                    {
                        ForumPost fp = new ForumPost();
                        CommonDataProvider.PopulatePostFromIDataReader(dr,fp);
                        postSet.Posts.Add ( fp );
                    }
					

                    dr.Close();
                }
				myConnection.Close();

				return postSet;
			}
        }

        /****************************************************************
        // GetForumsRequiringModeration
        //
        /// <summary>
        /// Returns a Moderated Foru
        /// </summary>
        //
        ****************************************************************/
        /*
        public override ArrayList GetForumsRequiringModeration(string username) {
            // Create Instance of Connection and Command Object
            using( SqlConnection myConnection = GetSqlConnection() ) {
            SqlCommand myCommand = new SqlCommand(databaseOwner + ".forums_GetModeratedForums", myConnection);
            SqlDataReader reader;
            ArrayList moderatedForums = new ArrayList();
            ModeratedForum moderatedForum;
            PostSet postSet;

            // Mark the Command as a SPROC
            myCommand.CommandType = CommandType.StoredProcedure;

            // Pass sproc parameters
            myCommand.Parameters.Add("@Username", SqlDbType.NVarChar, 50).Value = username;

            // Execute the command
            myConnection.Open();
            reader = myCommand.ExecuteReader();

            // Loop through the returned results
            while (reader.Read()) {

                // Populate all the forum details
                moderatedForum = new ModeratedForum();
                moderatedForum = (ModeratedForum) PopulateForumFromIDataReader(reader);

                // Get all the posts in the forum awaiting moderation
                //
                postSet = GetModeratedPostsByForumId(moderatedForum.ForumID);
                moderatedForum.PostsAwaitingModeration = postSet;
            }

            myConnection.Close();

            return moderatedForums;

        }
*/


        #region #### GetUsers ####
        /****************************************************************
        // GetUserAvatar
        //
        /// <summary>
        /// Returns a users avatar if it exists
        /// </summary>
        //
        ****************************************************************/
        public override Avatar GetUserAvatar(int userID) {
            // Create Instance of Connection and Command Object
            using( SqlConnection myConnection = GetSqlConnection() ) {
                SqlCommand myCommand = new SqlCommand(databaseOwner + ".cs_User_Avatar", myConnection);
                Avatar avatar = null;
                SqlDataReader reader;

                // Mark the Command as a SPROC
                myCommand.CommandType = CommandType.StoredProcedure;

                // Pass sproc parameters
                myCommand.Parameters.Add("@UserID", SqlDbType.Int).Value = userID;
                myCommand.Parameters.Add(this.SettingsIDParameter());

                // Execute the command
                myConnection.Open();

                using(reader = myCommand.ExecuteReader(CommandBehavior.CloseConnection))
                {
                    while(reader.Read()) 
                    {
                        avatar = PopulateAvatarFromIReader(reader);
                    }
                    reader.Close();
                }

                myConnection.Close();

                return avatar;
            }
        }


        public override int UserCount(UserQuery query)
        {
            using( SqlConnection myConnection = GetSqlConnection() ) 
            {
                SqlCommand myCommand = new SqlCommand(databaseOwner + ".cs_users_count_Get", myConnection);
                UserSet userSet = new UserSet();

                // Mark the Command as a SPROC
                myCommand.CommandType = CommandType.StoredProcedure;

                // Pass sproc parameters
                //
                myCommand.Parameters.Add("@UserAccountStatus", SqlDbType.SmallInt).Value = (int) query.Status;
                myCommand.Parameters.Add("@FilterIncludesEmailAddress", SqlDbType.Bit).Value = query.SearchEmail;
                myCommand.Parameters.Add("@IncludeHiddenUsers", SqlDbType.Bit).Value = query.IncludeHiddenUsers;
                myCommand.Parameters.Add(this.SettingsIDParameter());

                if ( (query.SearchText == ResourceManager.GetString("AlphaPicker_All")) || (query.SearchText == null) ) 
                {
                    myCommand.Parameters.Add("@UsernameFilter", SqlDbType.NVarChar, 64).Value = DBNull.Value;
                } 
                else 
                {

                    // Do wild card replacement
                    //
                    query.SearchText = query.SearchText.Replace("*", "%");

                    myCommand.Parameters.Add("@UsernameFilter", SqlDbType.NVarChar, 64).Value = query.SearchText;
                }

                // Execute the command
                //
                myConnection.Open();
                int userCount = (int)myCommand.ExecuteScalar();
                myConnection.Close();

                return userCount;
            }

        }


        /****************************************************************
        // GetUsers
        //
        /// <summary>
        /// Returns a collection of all users.
        /// </summary>
        //
        ****************************************************************/
        public override UserSet GetUsers(UserQuery query, bool returnModerationCounters ) {

            // Create Instance of Connection and Command Object
            //
			using( SqlConnection myConnection = GetSqlConnection() ) {
				SqlCommand myCommand = new SqlCommand(databaseOwner + ".cs_users_Get", myConnection);
				UserSet userSet = new UserSet();

				// Mark the Command as a SPROC
				myCommand.CommandType = CommandType.StoredProcedure;

				// Pass sproc parameters
				//
				myCommand.Parameters.Add("@PageIndex", SqlDbType.Int).Value = query.PageIndex;
				myCommand.Parameters.Add("@PageSize", SqlDbType.Int).Value = SQLHelper.GetSafeSqlInt(query.PageSize) ;
				myCommand.Parameters.Add("@sqlPopulate", SqlDbType.NText).Value = SqlGenerator.BuildMemberQuery(query, databaseOwner);
				myCommand.Parameters.Add("@SearchText", SqlDbType.NVarChar).Value = query.SearchText;
                myCommand.Parameters.Add("@ReturnModerationCounters", SqlDbType.Bit).Value = returnModerationCounters;
                myCommand.Parameters.Add(this.SettingsIDParameter());

				// Execute the command
				//
				myConnection.Open();
                using(SqlDataReader dr = myCommand.ExecuteReader(CommandBehavior.CloseConnection))
                {

                    // Populate the collection of users
                    //
                    while (dr.Read())
                        userSet.Users.Add(cs_PopulateUserFromIDataReader(dr,false,returnModerationCounters));

                    // Do we want the total records (it is always returned by sproc)?
                    //
                    if (query.IncludeRecordCount) 
                    {
                        dr.NextResult();

                        dr.Read();
                        userSet.TotalRecords = (int) dr[0];
                    }

                    dr.Close();
                }
				myConnection.Close();

				return userSet;
			}
        }

		public override User GetAnonymousUser( int settingsID ) {
			int userId;
			using( SqlConnection myConnection = GetSqlConnection() ) {
				SqlCommand myCommand = new SqlCommand(databaseOwner + ".cs_GetAnonymousUserID", myConnection);

				myCommand.CommandType = CommandType.StoredProcedure;
				myCommand.Parameters.Add( "@SettingsID", SqlDbType.Int).Value = settingsID;
				SqlParameter paramUserId = myCommand.Parameters.Add( "@UserID", SqlDbType.Int );
				paramUserId.Direction = ParameterDirection.Output;
				
				myConnection.Open();
				myCommand.ExecuteNonQuery();
                myConnection.Close();

				userId = (int)myCommand.Parameters["@UserID"].Value;
			}

			return GetUser( userId, "Anonymous", true, true, String.Empty );
		}
        #endregion

        #region #### Roles ####

        public override UserSet UsersInRole(int pageIndex, int pageSize, SortUsersBy sortBy, CommunityServer.Components.SortOrder sortOrder, Guid roleID, UserAccountStatus accountStatus, bool returnRecordCount) 
        {
            // Create Instance of Connection and Command Object
			using( SqlConnection myConnection = GetSqlConnection() ) {
				SqlCommand myCommand = new SqlCommand(databaseOwner + ".cs_users_UsersInRole_Get", myConnection);
				UserSet u = new UserSet();
			
				// Mark the Command as a SPROC
				myCommand.CommandType = CommandType.StoredProcedure;

				myCommand.Parameters.Add("@PageIndex", SqlDbType.Int).Value = pageIndex;
				myCommand.Parameters.Add("@PageSize", SqlDbType.Int).Value = SQLHelper.GetSafeSqlInt(pageSize);
				myCommand.Parameters.Add("@SortBy", SqlDbType.Int).Value = (int) sortBy;
				myCommand.Parameters.Add("@SortOrder", SqlDbType.Int).Value = (int) sortOrder;
				myCommand.Parameters.Add("@RoleID", SqlDbType.UniqueIdentifier).Value = roleID;
				myCommand.Parameters.Add("@UserAccountStatus", SqlDbType.SmallInt).Value = (int) accountStatus;
				myCommand.Parameters.Add("@ReturnRecordCount", SqlDbType.Bit).Value = returnRecordCount;
                myCommand.Parameters.Add(this.SettingsIDParameter());

				// Execute the command
				myConnection.Open();
                using(SqlDataReader dr = myCommand.ExecuteReader(CommandBehavior.CloseConnection))
                {

                    while(dr.Read()) 
                    {
                        u.Users.Add( cs_PopulateUserFromIDataReader(dr));
                    }

                    // Are we expecting the total records?
                    //
                    if (returnRecordCount) 
                    {
                        dr.NextResult();

                        dr.Read();
                        u.TotalRecords = (int) dr[0];
                    }

                    dr.Close();
                }
				myConnection.Close();

				return u;
			}
        }

		/// <summary>
		/// Retrieves information about a particular Role.
		/// </summary>
		public override Role GetRole(Guid roleID, string RoleName) {

			// Create Instance of Connection and Command Object
			using( SqlConnection myConnection = GetSqlConnection() ) {
				SqlCommand myCommand = new SqlCommand(databaseOwner + ".cs_Role_Get", myConnection);
			
				// Mark the Command as a SPROC
				myCommand.CommandType = CommandType.StoredProcedure;

				if( roleID == Guid.Empty )
					myCommand.Parameters.Add("@RoleID", SqlDbType.UniqueIdentifier).Value = DBNull.Value;
				else
					myCommand.Parameters.Add("@RoleID", SqlDbType.UniqueIdentifier).Value = roleID;
                myCommand.Parameters.Add("@RoleName", SqlDbType.NVarChar).Value = RoleName;
                myCommand.Parameters.Add(this.SettingsIDParameter());

				// Execute the command
				myConnection.Open();
                Role r = null;
                using(SqlDataReader dr = myCommand.ExecuteReader(CommandBehavior.CloseConnection))
                {
                    if(dr.Read())
                    {
                        r = PopulateRoleFromIDataReader(dr);
                    }

                    dr.Close();
                    myConnection.Close();
                }

                myConnection.Close();

                if(r == null)
				throw new CSException(CSExceptionType.RoleNotFound, roleID.ToString());

                return r;
			}
		}


        /****************************************************************
        // AddForumToRole
        //
        /// <summary>
        /// Adds a forum to a given role
        /// </summary>
        /// <param name="SectionID">The id for the forum to be added to the role</param>
        /// <param name="role">The role the user will be added to</param>
        //
        ****************************************************************/
        public override void AddForumToRole(int forumID, Guid roleID) {
            // Create Instance of Connection and Command Object
			using( SqlConnection myConnection = GetSqlConnection() ) {
				SqlCommand myCommand = new SqlCommand(databaseOwner + ".forums_Roles_AddForum", myConnection);

				// Mark the Command as a SPROC
				myCommand.CommandType = CommandType.StoredProcedure;

				// Add Parameters to SPROC
				myCommand.Parameters.Add("@SectionID", SqlDbType.Int, 4).Value = forumID;
				myCommand.Parameters.Add("@RoleID", SqlDbType.UniqueIdentifier, 4).Value = roleID;

				myConnection.Open();
				myCommand.ExecuteNonQuery();
				myConnection.Close();
			}
        }


        /****************************************************************
        // RemoveForumFromRole
        //
        /// <summary>
        /// Removes a forum from a given permissions role.
        /// </summary>
        /// <param name="SectionID">The forum ID for the forum to remove from the role.</param>
        /// <param name="roleID">The role ID of the user will be removed from</param>
        //
        ****************************************************************/
        public override void RemoveForumFromRole(int forumID, Guid roleID) {
            // Create Instance of Connection and Command Object
			using( SqlConnection myConnection = GetSqlConnection() ) {
				SqlCommand myCommand = new SqlCommand(databaseOwner + ".forums_Roles_RemoveForum", myConnection);

				// Mark the Command as a SPROC
				myCommand.CommandType = CommandType.StoredProcedure;

				// Add Parameters to SPROC
				myCommand.Parameters.Add("@SectionID", SqlDbType.Int, 4).Value = forumID;
				myCommand.Parameters.Add("@RoleID", SqlDbType.UniqueIdentifier, 4).Value = roleID;

				myConnection.Open();
				myCommand.ExecuteNonQuery();
				myConnection.Close();
			}
        }

		/// <summary>
		/// Adds a new forum.
		/// </summary>
		/// <param name="forum">A Forum object instance that defines the variables for the new forum to
		/// be added.  The Forum object properties used to create the new forum are: Name, Description,
		/// Moderated, and DaysToView.</param>
		public override Guid CreateUpdateDeleteRole(Role role, string applicationName, DataProviderAction action) {
			// Create Instance of Connection and Command Object
			using( SqlConnection myConnection = GetSqlConnection() ) {
				SqlCommand myCommand = new SqlCommand(databaseOwner + ".cs_Roles_CreateUpdateDelete", myConnection);
				myCommand.CommandType = CommandType.StoredProcedure;
				Guid roleID = Guid.Empty;

                myCommand.Parameters.Add(this.SettingsIDParameter());

				// Set the forum id
				//
				if (action == DataProviderAction.Create)
					myCommand.Parameters.Add("@RoleID", SqlDbType.UniqueIdentifier).Direction = ParameterDirection.Output;
				else
					myCommand.Parameters.Add("@RoleID", SqlDbType.UniqueIdentifier).Value = role.RoleID;

				// Are we doing a delete?
				//
				if (action == DataProviderAction.Delete)
					myCommand.Parameters.Add("@DeleteRole", SqlDbType.Bit).Value = true;

				// Are we doing an update or add?
				//
				if ( (action == DataProviderAction.Update) || (action == DataProviderAction.Create) ) {
                        
					myCommand.Parameters.Add("@Name", SqlDbType.NVarChar, 256).Value = role.Name;
					myCommand.Parameters.Add("@Description", SqlDbType.NVarChar, 512).Value = role.Description;
				}

				// Execute the command
				//
				myConnection.Open();
				myCommand.ExecuteNonQuery();
                myConnection.Close();

				// Get the forum ID
				//
				if (action == DataProviderAction.Create)
					roleID = (Guid) myCommand.Parameters["@RoleID"].Value;
				else
					roleID = role.RoleID;

				

				return roleID;
			}
		}
            
        /****************************************************************
        // GetRoles
        //
        /// <summary>
        /// Returns a string array of role names that the user belongs to
        /// </summary>
        /// <param name="username">username to find roles for</param>
        //
        ****************************************************************/
        public override Hashtable GetRoles(int userID) {
            Hashtable roles = new Hashtable();

            // Create Instance of Connection and Command Object
			using( SqlConnection myConnection = GetSqlConnection() ) {
				SqlCommand myCommand = new SqlCommand(databaseOwner + ".cs_Roles_Get", myConnection);

				// Mark the Command as a SPROC
				myCommand.CommandType = CommandType.StoredProcedure;

				// Add Parameters to SPROC
				myCommand.Parameters.Add("@UserID", SqlDbType.Int).Value = userID;
                myCommand.Parameters.Add(this.SettingsIDParameter());

				// Open the database connection and execute the command
				SqlDataReader dr;

				myConnection.Open();
                using(dr = myCommand.ExecuteReader(CommandBehavior.CloseConnection))
                {

                    while (dr.Read())
                    {
                        Role role = PopulateRoleFromIDataReader(dr);
                        roles.Add( role.RoleID, role );
                    }

                    dr.Close();
                }

				myConnection.Close();

				return roles;
			}
        }

        #endregion

        #region #### Forum Permissions ####

        /****************************************************************
        // UpdateForumPermission
        //
        /// <summary>
        /// 
        /// </summary>
        /// 
        //
        ****************************************************************/
        public override void CreateUpdateDeletePermission(PermissionBase p, DataProviderAction action) {
            // Create Instance of Connection and Command Object
			using( SqlConnection myConnection = GetSqlConnection() ) {
				SqlCommand myCommand = new SqlCommand(databaseOwner + ".cs_Section_Permission_CreateUpdateDelete", myConnection);

				// Mark the Command as a SPROC
				myCommand.CommandType = CommandType.StoredProcedure;

				// Add Parameters to SPROC
				myCommand.Parameters.Add(this.SettingsIDParameter());
				myCommand.Parameters.Add("@SectionID", SqlDbType.Int).Value			    = p.SectionID;
				myCommand.Parameters.Add("@RoleID", SqlDbType.UniqueIdentifier).Value	= p.RoleID;
				myCommand.Parameters.Add("@Action", SqlDbType.Int).Value				= action;
				myCommand.Parameters.Add("@AllowMask", SqlDbType.BigInt).Value			= (long)p.AllowMask;
				myCommand.Parameters.Add("@DenyMask", SqlDbType.BigInt).Value			= (long)p.DenyMask;
                myCommand.Parameters.Add("@ApplicationType", SqlDbType.SmallInt).Value			= p.ApplicationType;

				myConnection.Open();
				myCommand.ExecuteNonQuery();
				myConnection.Close();
			}
        }

        #endregion


        #region #### Anonymous Users ####

//        public override int UpdateAnonymousUsers (Hashtable anonymousUsers, int settingsID) {
//
//            // Create Instance of Connection and Command Object
//			using( SqlConnection myConnection = GetSqlConnection() ) {
//				SqlCommand myCommand = new SqlCommand(databaseOwner + ".cs_User_Anonymous_Update", myConnection);
//				int totalAnonymousUsers = 0;
//
//				// Mark the Command as a SPROC
//				myCommand.CommandType = CommandType.StoredProcedure;
//
//				// Set parameters
//				//
//				myCommand.Parameters.Add("@UserID", SqlDbType.Char, 36);
//				myCommand.Parameters.Add("@LastActivity", SqlDbType.DateTime);
//				myCommand.Parameters.Add("@LastAction", SqlDbType.NVarChar, 1024);
//                myCommand.Parameters.Add("@SettingsID",SqlDbType.Int,4);
//
//				// Open the connection
//				//
//				myConnection.Open();
//
//				foreach (string key in anonymousUsers.Keys) {
//                
//					if (anonymousUsers[key] is User) {
//						User user = (User) anonymousUsers[key];
//
//						myCommand.Parameters["@UserID"].Value = key;
//						myCommand.Parameters["@LastActivity"].Value = user.LastActivity;
//						myCommand.Parameters["@LastAction"].Value = user.LastAction;
//                        myCommand.Parameters["@SettingsID"].Value = settingsID;
//
//						myCommand.ExecuteNonQuery();
//					}
//
//				}
//
//				// Set the command to use a different stored procedure
//				//
//				myCommand.CommandText = databaseOwner + ".cs_User_Anonymous_Count";
//
//				// Clear the existing parameters
//				//
//				myCommand.Parameters.Clear();
//
//				// Add the new parameters
//				//
//                //HardCoded for beta 1 - ScottW - 11/12/2004
//				myCommand.Parameters.Add("@TimeWindow", SqlDbType.Int).Value = 15;
//				myCommand.Parameters.Add("@AnonymousUserCount", SqlDbType.Int).Direction = ParameterDirection.Output;
//                myCommand.Parameters.Add("@SettingsID",SqlDbType.Int,4).Value = settingsID;
//
//				myCommand.ExecuteNonQuery();
//
//				// Get the total anonymous users
//				//
//				totalAnonymousUsers = (int) myCommand.Parameters["@AnonymousUserCount"].Value;
//
//				// Close the connection
//				//
//				myConnection.Close();
//
//				return totalAnonymousUsers;
//			}
//        }
//

        #endregion

        #region #### Forum Group ####
        /****************************************************************
        // AddForumGroup
        //
        /// <summary>
        /// Creates a new forum group, and exception is raised if the
        /// forum group already exists.
        /// </summary>
        /// <param name="forumGroupName">Name of the forum group to create</param>
        //
        ****************************************************************/
        public override int CreateUpdateDeleteGroup(Group group, DataProviderAction action) {

            // Create Instance of Connection and Command Object
			using( SqlConnection myConnection = GetSqlConnection() ) {
				SqlCommand myCommand = new SqlCommand(databaseOwner + ".cs_Group_CreateUpdateDelete", myConnection);
				int forumGroupID = group.GroupID;

				// Mark the Command as a SPROC
				myCommand.CommandType = CommandType.StoredProcedure;

				// Add Parameters to SPROC
				//
				if (action == DataProviderAction.Create)
					myCommand.Parameters.Add("@GroupID", SqlDbType.Int).Direction = ParameterDirection.Output;
				else
					myCommand.Parameters.Add("@GroupID", SqlDbType.Int).Value = group.GroupID;

				myCommand.Parameters.Add("@Name", SqlDbType.NVarChar, 256).Value = group.Name;
				myCommand.Parameters.Add("@Description", SqlDbType.NVarChar, 1000).Value = group.Description;
				myCommand.Parameters.Add("@Action", SqlDbType.Int).Value = action;
                myCommand.Parameters.Add("@ApplicationType",SqlDbType.SmallInt).Value = group.ApplicationType;
                myCommand.Parameters.Add(this.SettingsIDParameter());

				// Open the connection
				myConnection.Open();
				myCommand.ExecuteNonQuery();
                myConnection.Close();

				if (action == DataProviderAction.Create)
					forumGroupID = (int) myCommand.Parameters["@GroupID"].Value;

				

				return forumGroupID;
			}
        }

        #endregion


 

        /// is the user tracking this thread?
		#region Removed IsUserTrackingThread Function

        /*public override bool IsUserTrackingThread(int threadID, string username) 
		{

            bool userIsTrackingPost = false; 

            // If username is null, don't continue
            if (username == null)
                return userIsTrackingPost;

            // Create Instance of Connection and Command Object
			using( SqlConnection myConnection = GetSqlConnection() ) {
				SqlCommand myCommand = new SqlCommand(databaseOwner + ".forums_IsUserTrackingPost", myConnection);

				// Mark the Command as a SPROC
				myCommand.CommandType = CommandType.StoredProcedure;

				// Add Parameters to SPROC
				myCommand.Parameters.Add("@ThreadID", SqlDbType.Int).Value = threadID;
				myCommand.Parameters.Add("@UserName", SqlDbType.NVarChar, 50).Value = username;

				// Execute the command
				myConnection.Open();
				SqlDataReader dr = myCommand.ExecuteReader();

				if (!dr.Read())
					return userIsTrackingPost;

				userIsTrackingPost = Convert.ToBoolean(dr["IsUserTrackingPost"]);

				dr.Close();
				myConnection.Close();

				return userIsTrackingPost;
			}
        }*/

		#endregion
    
		public override void UpdateUserSubscriptions(User user)
		{
			using (SqlConnection connection = GetSqlConnection())
			{
				SqlCommand command = new SqlCommand(databaseOwner + ".cs_system_UpdateUserSubscriptions", connection);

				command.CommandType = CommandType.StoredProcedure;

				command.Parameters.Add("@UserID", SqlDbType.Int).Value = user.UserID;
				command.Parameters.Add(this.SettingsIDParameter());

				connection.Open();
				command.ExecuteNonQuery();
				connection.Close();
			}
		}


		public override  void RemoveThreadTracking(int SectionID, int UserID) 
		{
			// Create Instance of Connection and Command Object
			using( SqlConnection myConnection = GetSqlConnection() ) 
			{
				SqlCommand myCommand = new SqlCommand(databaseOwner + ".cs_RemoveAllPostTracking", myConnection);

				// Mark the Command as a SPROC
				myCommand.CommandType = CommandType.StoredProcedure;

				// Add Parameters to SPROC
				myCommand.Parameters.Add("@UserID", SqlDbType.Int, 4).Value = UserID;
				myCommand.Parameters.Add("@SectionID", SqlDbType.Int, 4).Value = SectionID;
				myCommand.Parameters.Add(this.SettingsIDParameter());

				// Execute the command
				myConnection.Open();
				myCommand.ExecuteNonQuery();
				myConnection.Close();
			}
		}

        public override void RemoveSectionTracking(int SectionID, int UserID) 
        {
            // Create Instance of Connection and Command Object
            using( SqlConnection myConnection = GetSqlConnection() ) 
            {
                SqlCommand myCommand = new SqlCommand(databaseOwner + ".cs_RemoveAllSectionTracking", myConnection);

                // Mark the Command as a SPROC
                myCommand.CommandType = CommandType.StoredProcedure;

                // Add Parameters to SPROC
                if (UserID <= 0)
                    myCommand.Parameters.Add("@UserID", SqlDbType.Int, 4).Value = System.DBNull.Value;
                else
                    myCommand.Parameters.Add("@UserID", SqlDbType.Int, 4).Value = UserID;

                if (SectionID <= 0)
                    myCommand.Parameters.Add("@SectionID", SqlDbType.Int, 4).Value = System.DBNull.Value;
                else
                    myCommand.Parameters.Add("@SectionID", SqlDbType.Int, 4).Value = SectionID;

                myCommand.Parameters.Add(this.SettingsIDParameter());

                // Execute the command
                myConnection.Open();
                myCommand.ExecuteNonQuery();
                myConnection.Close();
            }
        }
    
        /// <summary>
        /// Reverses a particular user's email thread tracking options for the thread that contains
        /// the post specified by PostID.  That is, if a User has email thread tracking turned on for
        /// a particular thread, a call to this method will turn off the email thread tracking; conversely,
        /// if a user has thread tracking turned off for a particular thread, a call to this method will
        /// turn it on.
        /// </summary>
        /// <param name="userID">The User whose email thread tracking options we wish to reverse.</param>
        /// <param name="threadid"></param>
        public override  void ReverseThreadTracking(int userID, int threadid) 
		{
            // Create Instance of Connection and Command Object
			using( SqlConnection myConnection = GetSqlConnection() ) {
				SqlCommand myCommand = new SqlCommand(databaseOwner + ".cs_ReverseTrackingOption", myConnection);

				// Mark the Command as a SPROC
				myCommand.CommandType = CommandType.StoredProcedure;

				// Add Parameters to SPROC
				myCommand.Parameters.Add("@UserID", SqlDbType.Int, 4).Value = userID;
				myCommand.Parameters.Add("@ThreadId", SqlDbType.Int, 4).Value = threadid;
                myCommand.Parameters.Add(this.SettingsIDParameter());

				// Execute the command
				myConnection.Open();
				myCommand.ExecuteNonQuery();
				myConnection.Close();
			}
        }
		public override void ExpireTemporaryPostAttachments(DateTime beforeDate)
		{
			// Create Instance of Connection and Command Object
			//
			using( SqlConnection connection = GetSqlConnection() ) 
			{
				SqlCommand myCommand = new SqlCommand(databaseOwner + ".cs_PostAttachment_Temp_Expire", connection);
				myCommand.CommandType = CommandType.StoredProcedure;

				// Add parameters
				//
				myCommand.Parameters.Add("@Date", SqlDbType.DateTime).Value = beforeDate;

				connection.Open();
				myCommand.ExecuteNonQuery();
				connection.Close();
			}
		}
        public override PostAttachment ToggleTemporaryPostAttachment(Guid temporaryID, string name, bool clearContent, int postID, int sectionID, int userID)
        {
			return ToggleTemporaryPostAttachment(temporaryID, name, clearContent, postID, sectionID, userID, 0, 0);
		}
		public override PostAttachment ToggleTemporaryPostAttachment(Guid temporaryID, string name, bool clearContent, int postID, int sectionID, int userID, int height, int width)
		{
			return ToggleTemporaryPostAttachment(temporaryID, name, clearContent, postID, sectionID, userID, height, width, false);
		}
		public override PostAttachment ToggleTemporaryPostAttachment(Guid temporaryID, string name, bool clearContent, int postID, int sectionID, int userID, int height, int width, bool deleteTempOnly)
        {
            // Create Instance of Connection and Command Object
            //
            using( SqlConnection connection = GetSqlConnection() ) 
            {
                SqlCommand myCommand = new SqlCommand(databaseOwner + ".cs_PostAttachment_ToggleTemporary", connection);
                myCommand.CommandType = CommandType.StoredProcedure;

                // Add parameters
                //
                myCommand.Parameters.Add("@AttachmentID", SqlDbType.UniqueIdentifier).Value = temporaryID;
                myCommand.Parameters.Add("@UserID", SqlDbType.Int).Value = userID;
                myCommand.Parameters.Add("@PostID", SqlDbType.Int).Value = postID;
                myCommand.Parameters.Add("@ClearContent", SqlDbType.Bit).Value = clearContent;
                myCommand.Parameters.Add("@FriendlyFileName", SqlDbType.NVarChar, 256).Value =  SQLHelper.StringOrNull(name);
                myCommand.Parameters.Add("@SectionID", SqlDbType.Int).Value = sectionID;
                myCommand.Parameters.Add(this.SettingsIDParameter());
				myCommand.Parameters.Add("@DeleteTempOnly", SqlDbType.Bit).Value = deleteTempOnly; //false by default


                connection.Open();
                PostAttachment attachment = null;

                using(SqlDataReader reader = myCommand.ExecuteReader(CommandBehavior.CloseConnection|CommandBehavior.SingleRow))
                {
                    
                    while(reader.Read()) 
                    {
                        attachment = PopulatePostAttachmentFromIDataRecord(reader);
                    }

                    reader.Close();
                }

                connection.Close();

                return attachment;
            }
        }

		public override PostAttachment GetTemporaryPostAttachment(Guid temporaryID, int sectionID, int userID)
		{
			// Create Instance of Connection and Command Object
			//
			using( SqlConnection connection = GetSqlConnection() ) 
			{
				SqlCommand myCommand = new SqlCommand(databaseOwner + ".cs_PostAttachment_Temp_Get", connection);
				myCommand.CommandType = CommandType.StoredProcedure;

				// Add parameters
				//
				myCommand.Parameters.Add("@AttachmentID", SqlDbType.UniqueIdentifier).Value = temporaryID;
				myCommand.Parameters.Add("@UserID", SqlDbType.Int).Value = userID;
				myCommand.Parameters.Add("@SectionID", SqlDbType.Int).Value = sectionID;
				myCommand.Parameters.Add(this.SettingsIDParameter());


				connection.Open();
				PostAttachment attachment = null;

				using(SqlDataReader reader = myCommand.ExecuteReader(CommandBehavior.CloseConnection|CommandBehavior.SingleRow))
				{
                    
					while(reader.Read()) 
					{
						attachment = PopulatePostAttachmentFromIDataRecord(reader);
					}

					reader.Close();
				}

				connection.Close();

				return attachment;
			}
		}

        public override PostAttachment GetPostAttachment (int postID, bool metaDataOnly) 
		{
            // Create Instance of Connection and Command Object
            //
			using( SqlConnection connection = GetSqlConnection() ) {
				SqlCommand myCommand = new SqlCommand(databaseOwner + ".cs_PostAttachment", connection);
				PostAttachment attachment = null;
				SqlDataReader reader;
				myCommand.CommandType = CommandType.StoredProcedure;


				// Add parameters
				//
				myCommand.Parameters.Add("@PostID", SqlDbType.Int).Value = postID;
                myCommand.Parameters.Add("@MetaOnly", SqlDbType.Bit).Value = metaDataOnly;

				connection.Open();

                using(reader = myCommand.ExecuteReader(CommandBehavior.CloseConnection|CommandBehavior.SingleRow))
                {

                    while(reader.Read()) 
                    {
                        attachment = PopulatePostAttachmentFromIDataRecord(reader);
                    }

                    reader.Close();
                }

				connection.Close();

				return attachment;
			}
        }


        public override void AddTemporaryPostAttachment(Guid guid, PostAttachment attachment) 
        {
            // Create Instance of Connection and Command Object
            //
            using( SqlConnection connection = GetSqlConnection() ) 
            {
                SqlCommand myCommand = new SqlCommand(databaseOwner + ".cs_PostAttachment_Temp_Create", connection);
                myCommand.CommandType = CommandType.StoredProcedure;

				byte[] content;

				if (attachment.Content == null)
					content = new byte[0];
				else if (attachment.Content is MemoryStream)
					content = ((MemoryStream)attachment.Content).ToArray();
				else
				{
					content = new byte[attachment.Content.Length];
					attachment.Content.Position = 0;
					attachment.Content.Read(content, 0, (int)attachment.Content.Length);
				}

                // Add parameters
                //
                myCommand.Parameters.Add("@AttachmentID", SqlDbType.UniqueIdentifier).Value = guid;
                myCommand.Parameters.Add("@UserID", SqlDbType.Int).Value = CSContext.Current.User.UserID;
                myCommand.Parameters.Add("@SectionID", SqlDbType.Int).Value = attachment.SectionID;
                myCommand.Parameters.Add("@FriendlyFileName", SqlDbType.NVarChar, 256).Value = attachment.FriendlyFileName;
                myCommand.Parameters.Add("@Filename", SqlDbType.NVarChar, 1024).Value = attachment.FileName;
				myCommand.Parameters.Add("@Content", SqlDbType.Image).Value = content;
                myCommand.Parameters.Add("@ContentType", SqlDbType.NVarChar, 50).Value = attachment.ContentType;
                myCommand.Parameters.Add("@ContentSize", SqlDbType.Int).Value = attachment.Length;
                myCommand.Parameters.Add("@IsRemote", SqlDbType.Bit).Value = attachment.IsRemote;

				myCommand.Parameters.Add("@Height", SqlDbType.Int).Value = attachment.Height;
				myCommand.Parameters.Add("@Width", SqlDbType.Int).Value = attachment.Width;

                myCommand.Parameters.Add(this.SettingsIDParameter());

                connection.Open();
                myCommand.ExecuteNonQuery();
                connection.Close();
            }
        }

        public override void AddPostAttachment(Post post, PostAttachment attachment) {
            // Create Instance of Connection and Command Object
            //
			using( SqlConnection connection = GetSqlConnection() ) 
			{
				SqlCommand myCommand = new SqlCommand(databaseOwner + ".cs_PostAttachment_Add", connection);
				myCommand.CommandType = CommandType.StoredProcedure;

				byte[] content;

				if (attachment.Content == null)
					content = new byte[0];
				else if (attachment.Content is MemoryStream)
					content = ((MemoryStream) attachment.Content).ToArray();
				else
				{
					content = new byte[attachment.Content.Length];
					attachment.Content.Position = 0;
					attachment.Content.Read(content, 0, (int)attachment.Content.Length);
				}

				// Add parameters
				//
				myCommand.Parameters.Add("@PostID", SqlDbType.Int).Value = post.PostID;
				myCommand.Parameters.Add("@UserID", SqlDbType.Int).Value = post.User.UserID;
				myCommand.Parameters.Add("@SectionID", SqlDbType.Int).Value = post.SectionID;
				myCommand.Parameters.Add("@Filename", SqlDbType.NVarChar, 1024).Value = attachment.FileName;
                myCommand.Parameters.Add("@FriendlyFileName", SqlDbType.NVarChar, 256).Value = attachment.FriendlyFileName;
				myCommand.Parameters.Add("@Content", SqlDbType.Image).Value = content;
				myCommand.Parameters.Add("@ContentType", SqlDbType.NVarChar, 50).Value = attachment.ContentType;
				myCommand.Parameters.Add("@ContentSize", SqlDbType.Int).Value = attachment.Length;
                myCommand.Parameters.Add("@IsRemote", SqlDbType.Bit).Value = attachment.IsRemote;
	
				myCommand.Parameters.Add("@Height", SqlDbType.Int).Value = attachment.Height;
				myCommand.Parameters.Add("@Width", SqlDbType.Int).Value = attachment.Width;
				
                myCommand.Parameters.Add(this.SettingsIDParameter());

				connection.Open();
				myCommand.ExecuteNonQuery();
				connection.Close();
			}
        }

		public override void DeletePostAttachment(int postID)
		{
			using( SqlConnection connection = GetSqlConnection() ) 
			{
				SqlCommand myCommand = new SqlCommand(databaseOwner + ".cs_PostAttachment_Delete", connection);
				myCommand.CommandType = CommandType.StoredProcedure;


				// Add parameters
				//
				myCommand.Parameters.Add("@PostID", SqlDbType.Int).Value = postID;

				connection.Open();
				myCommand.ExecuteNonQuery();
				connection.Close();
			}
		}



        /*********************************************************************************/

        /************************ FORUM FUNCTIONS ***********************
                 * These functions return information about a forum.
                 * are called from the WebForums.Forums class.
                 * **************************************************************/
    



        public override Hashtable GetGroups(ApplicationType appType, bool requireModeration) 
		{
            Hashtable forumGroups = new Hashtable();

            // Connect to the database
            //
			using( SqlConnection myConnection = GetSqlConnection() ) {
				SqlCommand myCommand = new SqlCommand(databaseOwner + ".cs_Groups_Get", myConnection);

				myCommand.Parameters.Add(this.SettingsIDParameter());
                myCommand.Parameters.Add("@ApplicationType", SqlDbType.SmallInt).Value = appType;
				myCommand.Parameters.Add("@RequireModeration", SqlDbType.Bit).Value = requireModeration;
				myCommand.CommandType = CommandType.StoredProcedure;


				// Execute the command
				myConnection.Open();
                using(SqlDataReader dr = myCommand.ExecuteReader(CommandBehavior.CloseConnection))
                {

                    while (dr.Read()) 
                    {
                        Group f = PopulateForumGroupFromIDataReader(dr);

                        forumGroups.Add(f.GroupID, f);

                    }

                    dr.Close();
                }
				myConnection.Close();

				return forumGroups;
			}
        }

		public override HybridDictionary GetSectionsRead(int userID) {

			HybridDictionary sectionsRead = new HybridDictionary();

			using(SqlConnection myConnection = GetSqlConnection()) {
				SqlCommand myCommand = new SqlCommand(databaseOwner + ".cs_Sections_GetRead", myConnection);
				SqlDataReader reader;
				myCommand.CommandType = CommandType.StoredProcedure;
				myCommand.Parameters.Add("@UserID", SqlDbType.Int).Value = userID;
				myCommand.Parameters.Add(this.SettingsIDParameter());
                
				myConnection.Open();
                using(reader = myCommand.ExecuteReader(CommandBehavior.CloseConnection))
                {

                    while (reader.Read()) 
                    {
                        sectionsRead.Add( reader["SectionID"], reader["LastActivity"]);
                    }
                    reader.Close();

                }
				myConnection.Close();

				return sectionsRead;
			}		
		}	
	
        /// <summary>
        /// Adds a new forum.
        /// </summary>
        /// <param name="section">A Forum object instance that defines the variables for the new forum to
        /// be added.  The Forum object properties used to create the new forum are: Name, Description,
        /// Moderated, and DaysToView.</param>
        public override int CreateUpdateDeleteSection(Section section, DataProviderAction action) 
		{
            // Create Instance of Connection and Command Object
			using( SqlConnection myConnection = GetSqlConnection() ) {
				SqlCommand myCommand = new SqlCommand(databaseOwner + ".cs_Section_CreateUpdateDelete", myConnection);
				myCommand.CommandType = CommandType.StoredProcedure;
				int forumID = -1;

				// Set the forum id
				//
				if (action == DataProviderAction.Create)
					myCommand.Parameters.Add("@SectionID", SqlDbType.Int).Direction = ParameterDirection.Output;
				else
					myCommand.Parameters.Add("@SectionID", SqlDbType.Int).Value = section.SectionID;

				// Are we doing a delete?
				//
				if (action == DataProviderAction.Delete)
					myCommand.Parameters.Add("@DeleteForum", SqlDbType.Bit).Value = true;

				// Are we doing an update or add?
				//
                if ( (action == DataProviderAction.Update) || (action == DataProviderAction.Create) ) 
                {

                    // To prevent a forum from being saved with a parent id that doesn't exist, we need to check sections with non-zero parent ids
                    // before creating or updating the child section.
                    if (section.ParentID != 0 && section.ApplicationType == ApplicationType.Forum)
                    {
                        Forum parent = Forums.GetForum(section.ParentID);
                        if (parent == null)
                        {
                            section.ParentID = 0;
                        }
                    }
                            
					myCommand.Parameters.Add("@Name", SqlDbType.NVarChar, 256).Value = section.Name;
					myCommand.Parameters.Add("@Url", SqlDbType.NVarChar, 512).Value = section.NavigateUrl;
					myCommand.Parameters.Add("@Description", SqlDbType.NVarChar, 3000).Value = section.Description;
					myCommand.Parameters.Add("@ParentID", SqlDbType.Int).Value = section.ParentID;
					myCommand.Parameters.Add("@GroupID", SqlDbType.Int).Value = section.GroupID;
					myCommand.Parameters.Add("@IsModerated", SqlDbType.Bit).Value = section.IsModerated;
					myCommand.Parameters.Add("@DisplayPostsOlderThan", SqlDbType.Int).Value = section.DefaultThreadDateFilter;
					myCommand.Parameters.Add("@IsActive", SqlDbType.Bit).Value = section.IsActive;
					myCommand.Parameters.Add("@EnablePostStatistics", SqlDbType.Bit).Value = section.EnablePostStatistics;
					myCommand.Parameters.Add("@EnablePostPoints", SqlDbType.Bit).Value = section.EnablePostPoints;
					myCommand.Parameters.Add("@EnableAutoDelete", SqlDbType.Bit).Value = section.EnableAutoDelete;
					myCommand.Parameters.Add("@EnableAnonymousPosting", SqlDbType.Bit).Value = section.EnableAnonymousPosting;
					myCommand.Parameters.Add("@AutoDeleteThreshold", SqlDbType.Int).Value = section.AutoDeleteThreshold;
					myCommand.Parameters.Add("@SortOrder", SqlDbType.Int, 4).Value = section.SortOrder;
					myCommand.Parameters.Add("@IsSearchable", SqlDbType.Bit,1).Value = section.IsSearchable;
					myCommand.Parameters.Add("@ApplicationType",SqlDbType.SmallInt, 1).Value = section.ApplicationType;
					myCommand.Parameters.Add("@ApplicationKey",SqlDbType.NVarChar, 256).Value = section.ApplicationKey;
					myCommand.Parameters.Add("@ForumType", SqlDbType.Int).Value = section.ForumType;
					myCommand.Parameters.Add("@DefaultLanguage", SqlDbType.NVarChar, 32).Value = Globals.IsNullorEmpty(section.DefaultLanguage) ? null : section.DefaultLanguage;

                    SerializerData data = section.GetSerializerData();


                    myCommand.Parameters.Add("@PropertyNames", SqlDbType.NText).Value = data.Keys;
                    myCommand.Parameters.Add("@PropertyValues", SqlDbType.NText).Value = data.Values;
				}

				// Add SettingsID (necessary for all methods
				myCommand.Parameters.Add(this.SettingsIDParameter());
				myCommand.Parameters.Add("@UserID", SqlDbType.Int,4).Value = CSContext.Current.User.UserID;

				// Execute the command
				//
				myConnection.Open();
				myCommand.ExecuteNonQuery();
                myConnection.Close();

				// Get the section ID
				//
				if (action == DataProviderAction.Create)
					forumID = (int) myCommand.Parameters["@SectionID"].Value;
				else
					forumID = section.SectionID;

				

				return forumID;
			}
        }



        /*********************************************************************************/

        /************************ USER FUNCTIONS ***********************
                 * These functions return information about a user.
                 * are called from the WebForums.Users class.
                 * *************************************************************/
    
        #region #### User ####

        /// <summary>
        /// Retrieves information about a particular user.
        /// </summary>
        /// <param name="Username">The name of the User whose information you are interested in.</param>
        /// <returns>A User object.</returns>
        /// <remarks>If a Username is passed in that is NOT found in the database, a UserNotFoundException
        /// exception is thrown.</remarks>
        public override  User GetUser(int userID, string username, bool isOnline, bool isEditable, string lastAction) {

			//Were do we look users up by userID? We should (if possible) switch to username. This would enable us to
			//use the SHS API directly.

          // Create Instance of Connection and Command Object
			    using( SqlConnection myConnection = GetSqlConnection() ) {
				    SqlCommand myCommand = new SqlCommand(databaseOwner + ".cs_user_Get", myConnection);
				    myCommand.CommandType = CommandType.StoredProcedure;

				    // Mark the Command as a SPROC

				    // Add Parameters to SPROC
				    if (username != null)
					    myCommand.Parameters.Add("@UserName", SqlDbType.NVarChar, 64).Value = username;

				    myCommand.Parameters.Add("@UserID", SqlDbType.Int).Value = userID;
				    myCommand.Parameters.Add("@IsOnline", SqlDbType.Bit).Value = isOnline;
				    myCommand.Parameters.Add("@LastAction", SqlDbType.NVarChar, 1024).Value = lastAction;
                    myCommand.Parameters.Add(this.SettingsIDParameter());

				    // Execute the command
				    myConnection.Open();
                    User u = null;
                    using(SqlDataReader dr = myCommand.ExecuteReader(CommandBehavior.CloseConnection))
                    {
                        if(dr.Read())
                        {
                            u = cs_PopulateUserFromIDataReader(dr,isEditable);
                        }
                        dr.Close();
                    }
				    myConnection.Close();

                    if(u == null)
                        throw new CSException(CSExceptionType.UserNotFound, username);
				    
                    return u;

			    }        
		    }
   
        #endregion


        #region #### Users Online ####
        /// <summary>
        /// Returns a depricated user collection of the user's currently online
        /// for the specified minutes. Only the username and whether they are an
        /// administrator is returned.
        /// </summary>
        /// <param name="pastMinutes">Minutes back in time</param>
        /// <returns></returns>
//        public override UsersOnLineSet WhoIsOnline(int pastMinutes) 
//        {
//
//            // Create Instance of Connection and Command Object
//			using( SqlConnection myConnection = GetSqlConnection() ) {
//				SqlCommand myCommand = new SqlCommand(databaseOwner + ".cs_users_Online", myConnection);
//				UsersOnLineSet users = new UsersOnLineSet();
//
//				// Mark the Command as a SPROC
//				myCommand.CommandType = CommandType.StoredProcedure;
//
//				// Add Parameters to SPROC
//				SqlParameter parameterUsername = new SqlParameter("@PastMinutes", SqlDbType.Int);
//				parameterUsername.Value = pastMinutes;
//				myCommand.Parameters.Add(parameterUsername);
//                myCommand.Parameters.Add(this.SettingsIDParameter());
//
//				// Execute the command
//				myConnection.Open();
//                using(SqlDataReader dr = myCommand.ExecuteReader(CommandBehavior.CloseConnection))
//                {
//                    // Get the members
//                    //
//                    while (dr.Read())
//                        users.Members.Add( cs_PopulateUserFromIDataReader(dr) );
//
//                    // Get the guests
//                    if (dr.NextResult()) 
//                    {
//
//                        while (dr.Read()) 
//                        {
//                            User user = new User();
//                            user.Username = ResourceManager.GetString("Guest");
//                            user.LastAction = (string) dr["LastAction"];
//                            user.LastActivity = (DateTime) dr["LastActivity"];
//
//                            users.Guests.Add(user);
//                        }
//
//                    }
//
//
//                    dr.Close();
//                }
//				myConnection.Close();
//
//				return users;
//			}        
//		}
        #endregion

        public override bool ValidateUserPasswordAnswer(object UserId, string answer)
        {
            //cs_users_ValidateAnswereAnswer
            using( SqlConnection myConnection = GetSqlConnection() ) 
            {
                using(SqlCommand myCommand = new SqlCommand(databaseOwner + ".cs_users_ValidateAnswereAnswer", myConnection))
                {
                    myCommand.CommandType = CommandType.StoredProcedure;
                    myCommand.Parameters.Add("@UserId", SqlDbType.UniqueIdentifier).Value = UserId;
                    myCommand.Parameters.Add("@PasswordAnswer", SqlDbType.NVarChar).Value = answer;

                    SqlParameter p = new SqlParameter("@IsValid", SqlDbType.Bit, 1);
                    p.Direction = ParameterDirection.Output;

                    myCommand.Parameters.Add(p);

                    myConnection.Open();
                    myCommand.ExecuteNonQuery();
                    myConnection.Close();

                    return (bool)p.Value;


                }
                

            }

        }

        /// <summary>
        /// This method creates a new user if possible.  If the username or
        /// email addresses already exist, an appropriate CreateUserStatus message is
        /// returned.
        /// </summary>
        /// <param name="user">The email for the new user account.</param>
        /// <returns>An CreateUserStatus enumeration value, indicating if the user was created successfully
        /// (CreateUserStatus.Created) or if the new user couldn't be created because of a duplicate
        /// Username (CreateUserStatus.DuplicateUsername) or duplicate email address (CreateUserStatus.DuplicateEmailAddress).</returns>
        /// <remarks>All User accounts created must consist of a unique Username and a unique
        /// Email address.</remarks>
        public override User CreateUpdateDeleteUser(User user, DataProviderAction action, out CreateUserStatus status) {

           UserProviderManager upm = new UserProviderManager(user);

			if(action == DataProviderAction.Create && !user.IsAnonymous)
                 upm.Create();
			else if(action == DataProviderAction.Update)
                upm.Update();

            // Create Instance of Connection and Command Object
            //
			using( SqlConnection myConnection = GetSqlConnection() ) {
				SqlCommand myCommand = new SqlCommand(databaseOwner + ".cs_user_CreateUpdateDelete", myConnection);
				myCommand.CommandType = CommandType.StoredProcedure;

				// Set the parameters
				//
				myCommand.Parameters.Add("@Action", SqlDbType.Int).Value = action;

				// Set the user id to the appropriate type
				//
				if (action == DataProviderAction.Create)
					myCommand.Parameters.Add("@cs_UserID", SqlDbType.Int).Direction = ParameterDirection.Output;
				else
					myCommand.Parameters.Add("@cs_UserID", SqlDbType.Int).Value = upm.CurrentUser.UserID;

				// Common parameters for update and add
				//
				if ( (action == DataProviderAction.Create) || (action == DataProviderAction.Update) ) {

					myCommand.Parameters.Add("@UserID", SqlDbType.UniqueIdentifier).Value = upm.CurrentUser.Member.ProviderUserKey;
					myCommand.Parameters.Add("@UserAccountStatus", SqlDbType.SmallInt).Value = (int) upm.CurrentUser.AccountStatus;
					myCommand.Parameters.Add("@IsAnonymous", SqlDbType.Bit).Value = upm.CurrentUser.IsAnonymous;
					myCommand.Parameters.Add("@IsIgnored", SqlDbType.Bit).Value = upm.CurrentUser.IsIgnored;
					myCommand.Parameters.Add("@ForceLogin", SqlDbType.Bit).Value = upm.CurrentUser.ForceLogin;
					myCommand.Parameters.Add("@AppUserToken", SqlDbType.VarChar, 128).Value = upm.CurrentUser.AppUserToken;

					// Values set in the cs_UserProfile table
					//
                    SerializerData data = upm.CurrentUser.GetSerializerData();

                    myCommand.Parameters.Add("@PropertyNames", SqlDbType.NText).Value = data.Keys;
                    myCommand.Parameters.Add("@PropertyValues", SqlDbType.NText).Value = data.Values;

					myCommand.Parameters.Add("@TimeZone", SqlDbType.Float).Value = upm.CurrentUser.Profile.Timezone;
					myCommand.Parameters.Add("@PostRank", SqlDbType.Binary, 1).Value = upm.CurrentUser.PostRank;
					myCommand.Parameters.Add("@PostSortOrder", SqlDbType.SmallInt).Value = upm.CurrentUser.PostSortOrder;
					myCommand.Parameters.Add("@IsAvatarApproved", SqlDbType.SmallInt).Value = upm.CurrentUser.IsAvatarApproved;
					myCommand.Parameters.Add("@ModerationLevel", SqlDbType.SmallInt).Value = (int) upm.CurrentUser.ModerationLevel;
					myCommand.Parameters.Add("@EnableThreadTracking", SqlDbType.SmallInt).Value = upm.CurrentUser.EnableThreadTracking;
					myCommand.Parameters.Add("@EnableAvatar", SqlDbType.SmallInt).Value = upm.CurrentUser.EnableAvatar;
					myCommand.Parameters.Add("@EnableDisplayInMemberList", SqlDbType.SmallInt).Value = upm.CurrentUser.EnableDisplayInMemberList;
					myCommand.Parameters.Add("@EnablePrivateMessages", SqlDbType.SmallInt).Value = upm.CurrentUser.EnablePrivateMessages;
					myCommand.Parameters.Add("@EnableOnlineStatus", SqlDbType.SmallInt).Value = upm.CurrentUser.EnableOnlineStatus;
                    myCommand.Parameters.Add("@EnableEmail", SqlDbType.SmallInt).Value = upm.CurrentUser.EnableEmail;
					myCommand.Parameters.Add("@EnableHtmlEmail", SqlDbType.SmallInt).Value = upm.CurrentUser.EnableHtmlEmail;
                    myCommand.Parameters.Add("@FavoritesShared", SqlDbType.Int).Value = upm.CurrentUser.FavoritesShared;
                    //EUPA Customization
                    myCommand.Parameters.Add("@SkillLevel", SqlDbType.Int).Value = upm.CurrentUser.SkillLevel;
                    myCommand.Parameters.Add("@Address", SqlDbType.NVarChar).Value = upm.CurrentUser.Address;
                    myCommand.Parameters.Add("@PostalCode", SqlDbType.NVarChar).Value = upm.CurrentUser.PostalCode;
                    myCommand.Parameters.Add("@Province", SqlDbType.NVarChar).Value = upm.CurrentUser.Province;
                    myCommand.Parameters.Add("@Male", SqlDbType.Bit).Value = upm.CurrentUser.Male;
                    myCommand.Parameters.Add("@Bday", SqlDbType.SmallDateTime).Value = upm.CurrentUser.BDay;
                    myCommand.Parameters.Add("@Email", SqlDbType.NVarChar).Value = upm.CurrentUser.Email;
                    myCommand.Parameters.Add("@Name", SqlDbType.NVarChar).Value = upm.CurrentUser.Profile.CommonName;


					myCommand.Parameters.Add("@AllowSiteToContact", SqlDbType.Bit).Value = upm.CurrentUser.AllowSiteToContact;
					myCommand.Parameters.Add("@AllowSitePartnersToContact", SqlDbType.Bit).Value = upm.CurrentUser.AllowSitePartnersToContact;
                    myCommand.Parameters.Add(this.SettingsIDParameter());

				}

				// Execute the command
				myConnection.Open();
            
				// Get the return code and cast to a status
				//
				status = (CreateUserStatus) Convert.ToInt32(myCommand.ExecuteScalar());
            
				// Get the forum ID
				//
				if ( (action == DataProviderAction.Create) && (status == CreateUserStatus.Created) )
					upm.CurrentUser.UserID = (int) myCommand.Parameters["@cs_UserID"].Value;

				myConnection.Close();

                switch(action)
                {
                    case DataProviderAction.Create:
                        if(status != CreateUserStatus.Created)
                    {
                            upm.Delete();
                             throw new CSException(status);
                    }
                        break;
                    case DataProviderAction.Update:
                        if(status != CreateUserStatus.Updated)
                    throw new CSException(status);
                        break;

                }

                return upm.CurrentUser;
			}
		}

		public override RenameUserStatus RenameUser(int userID, string desiredUserName, bool ignoreDisallowedNames)
		{
			using (SqlConnection connection = GetSqlConnection())
			{
				SqlCommand command = new SqlCommand(databaseOwner + ".cs_User_Rename", connection);
				command.CommandType = CommandType.StoredProcedure;

				command.Parameters.Add("@UserID", SqlDbType.Int).Value = userID;
				command.Parameters.Add("@DesiredUserName", SqlDbType.NVarChar, 256).Value = desiredUserName;
				command.Parameters.Add("@IgnoreDisallowedNames", SqlDbType.Bit).Value = ignoreDisallowedNames;

				connection.Open();
				RenameUserStatus status = (RenameUserStatus)Convert.ToInt32(command.ExecuteScalar());
				connection.Close();

				return status;
			}
		}

		public override DeleteUserStatus DeleteUser(int userID, string reassignUserName)
		{
			using (SqlConnection connection = GetSqlConnection())
			{
				SqlCommand command = new SqlCommand(databaseOwner + ".cs_User_Delete", connection);
				command.CommandType = CommandType.StoredProcedure;

				command.Parameters.Add("@UserID", SqlDbType.Int).Value = userID;
				command.Parameters.Add("@ReassignUserName", SqlDbType.NVarChar, 256).Value = reassignUserName;


				connection.Open();
				DeleteUserStatus status = (DeleteUserStatus)Convert.ToInt32(command.ExecuteScalar());
				connection.Close();

				return status;
			}
		}
        
        /*********************************************************************************/

        /********************* MODERATION FUNCTIONS *********************
                 * These functions are used to perform moderation.  They are called
                 * from the WebForums.Moderate class.
                 * **************************************************************/

		public override void ToggleUserForceLogin(User user)
		{

			using( SqlConnection myConnection = GetSqlConnection() ) 
			{
				SqlCommand myCommand = new SqlCommand(databaseOwner + ".cs_User_ToggleSettings", myConnection);

				// Mark the Command as a SPROC
				myCommand.CommandType = CommandType.StoredProcedure;

				// Add Parameters to SPROC
				myCommand.Parameters.Add("@UserID", SqlDbType.Int).Value = user.UserID;
				myCommand.Parameters.Add("@ModerationLevel", SqlDbType.Int).Value = (int) user.ModerationLevel;
				myCommand.Parameters.Add("@UserAccountStatus", SqlDbType.Int).Value = (int) user.AccountStatus;
				myCommand.Parameters.Add("@IsAvatarApproved", SqlDbType.Bit).Value = user.IsAvatarApproved;
				myCommand.Parameters.Add("@ForceLogin", SqlDbType.Bit).Value = !user.ForceLogin;
				myCommand.Parameters.Add("@ModeratorID", SqlDbType.Int).Value = 0;
				myCommand.Parameters.Add(this.SettingsIDParameter());

				// Execute the command
				myConnection.Open();
				myCommand.ExecuteNonQuery();
				myConnection.Close();
			}
		}

        /*********************************************************************************/




        /*********************************************************************************/

        /********************* EMAIL FUNCTIONS *********************
                 * These functions are used to perform email functionality.
                 * They are called from the WebForums.Email class
                 * *********************************************************/

        #region #### Emails ####

		public override EmailSubscriptionType GetSectionSubscriptionType(int sectionID, int UserID)
		{
			using( SqlConnection myConnection = GetSqlConnection() ) 
			{
				SqlCommand myCommand = new SqlCommand(databaseOwner + ".cs_GetSectionSubscriptionType", myConnection);
				myCommand.CommandType = CommandType.StoredProcedure;
				myCommand.Parameters.Add("@UserID", SqlDbType.Int).Value = UserID;
				myCommand.Parameters.Add("@SectionID", SqlDbType.Int).Value = sectionID;
				myCommand.Parameters.Add("@SubType", SqlDbType.Int).Value = 0;
				myCommand.Parameters.Add(this.SettingsIDParameter());
				myConnection.Open();
                int iType = 0;
                using(SqlDataReader reader = myCommand.ExecuteReader(CommandBehavior.SingleRow|CommandBehavior.CloseConnection))
                {
                    
                    if(reader.Read())
                        iType = (int) reader["SubscriptionType"];

                    reader.Close();
                }
				myConnection.Close();
				return (EmailSubscriptionType)iType;
			}
		}

		public override Hashtable GetSubscriptionTypesBySectionID(int UserID)
		{
			using( SqlConnection myConnection = GetSqlConnection() ) 
			{
				SqlCommand myCommand = new SqlCommand(databaseOwner + ".cs_GetSectionSubscriptionTypes", myConnection);
				myCommand.CommandType = CommandType.StoredProcedure;
				myCommand.Parameters.Add("@UserID", SqlDbType.Int).Value = UserID;
				myCommand.Parameters.Add(this.SettingsIDParameter());

				myConnection.Open();
				Hashtable typesBySectionID = new Hashtable();
				using(SqlDataReader reader = myCommand.ExecuteReader(CommandBehavior.CloseConnection))
				{
					while(reader.Read())
						typesBySectionID[(int) reader["SectionID"]] = (int) reader["SubscriptionType"];

					reader.Close();
				}
				myConnection.Close();
				return typesBySectionID;
			}
		}

		public override void SetSectionSubscriptionType(int sectionID, int UserID, EmailSubscriptionType SubscriptionType)
		{
			using( SqlConnection myConnection = GetSqlConnection() ) 
			{
				SqlCommand myCommand = new SqlCommand(databaseOwner + ".cs_SetSectionSubscriptionType", myConnection);
				myCommand.CommandType = CommandType.StoredProcedure;
				myCommand.Parameters.Add("@UserID", SqlDbType.Int).Value = UserID;
				myCommand.Parameters.Add("@SectionID", SqlDbType.Int).Value = sectionID;
				myCommand.Parameters.Add("@SubType", SqlDbType.Int).Value = SubscriptionType;
				myCommand.Parameters.Add(this.SettingsIDParameter());
				myConnection.Open();
				myCommand.ExecuteNonQuery();
				myConnection.Close();
			}
		}

        /// <summary>
        /// Returns a list of Users that have email thread tracking turned on for a particular post
        /// in a particular thread.
        /// </summary>
        /// <param name="postID">The ID of the Post of the thread to send a notification to.</param>
        /// <returns>A ArrayList listing the users who have email thread tracking turned on for
        /// the specified thread.</returns>
        public override Hashtable GetEmailsTrackingThread(int postID) {

			using( SqlConnection myConnection = GetSqlConnection() ) {
				SqlCommand myCommand = new SqlCommand(databaseOwner + ".cs_Emails_TrackingThread", myConnection);
				myCommand.CommandType = CommandType.StoredProcedure;
				Hashtable users = new Hashtable();

				// Add Parameters to SPROC
				myCommand.Parameters.Add("@PostID", SqlDbType.Int).Value = postID;
                myCommand.Parameters.Add(this.SettingsIDParameter());
    
				// Execute the command
				myConnection.Open();
                using(SqlDataReader dr = myCommand.ExecuteReader(CommandBehavior.CloseConnection))
                {
                    while (dr.Read()) 
                    {
                        User user = new User();
						if(dr["UserID"] != DBNull.Value)
							user.UserID = Convert.ToInt32(dr["UserID"]);
                        user.Email =  dr["Email"] as string;
                        user.EnableHtmlEmail = Convert.ToBoolean(dr["EnableHtmlEmail"]);
						user.SetExtendedAttribute("SubscriptionType", "3");
                        if ((!Globals.IsNullorEmpty(user.Email)) && (!users.ContainsKey(user.Email)))
                            users.Add(user.Email, user);
                    }
                
                    dr.Close();
                }
				myConnection.Close();

				return users;
			}
        }

		/// <summary>
		/// Returns a list of Users that have email forum tracking turned on for a particular forum.
		/// </summary>
		/// <param name="postID">The ID of the Post of the forum to send a notification to.</param>
		/// <returns>A ArrayList listing the users who have email forum tracking turned on for
		/// the specified thread.</returns>
		public override Hashtable GetEmailsTrackingSectionByPostID(int postID) 
		{

			using( SqlConnection myConnection = GetSqlConnection() ) {
				SqlCommand myCommand = new SqlCommand(databaseOwner + ".cs_Emails_TrackingSection", myConnection);
				myCommand.CommandType = CommandType.StoredProcedure;
				Hashtable users = new Hashtable();

				// Add Parameters to SPROC
				myCommand.Parameters.Add("@PostID", SqlDbType.Int).Value = postID;
                myCommand.Parameters.Add(this.SettingsIDParameter());
    
				// Execute the command
				myConnection.Open();
                using(SqlDataReader dr = myCommand.ExecuteReader(CommandBehavior.CloseConnection))
                {
                    while (dr.Read()) 
                    {
                        User user = new User();
						if(dr["UserID"] != DBNull.Value)
							user.UserID = Convert.ToInt32(dr["UserID"]);
                        user.Email = (string) dr["Email"];
                        user.EnableHtmlEmail = Convert.ToBoolean(dr["EnableHtmlEmail"]);
						user.SetExtendedAttribute("SubscriptionType", Convert.ToInt32(dr["SubscriptionType"]).ToString());
                        if ((!Globals.IsNullorEmpty(user.Email)) && (!users.ContainsKey(user.Email)))
                            users.Add(user.Email, user);
                    }

                    dr.Close();
                }
				myConnection.Close();

				return users;
			}
		}

        #endregion


        /*********************************************************************************/

        /**************** MODERATOR LISTING FUNCTIONS **************
                 * These functions are used to edit/update/work with the list
                 * of forums a user can moderate.
                 * *********************************************************/

  
        public override void SectionMoveOrReOrder(int GroupID, int SectionID, int Index)
        {
            using( SqlConnection myConnection = GetSqlConnection() ) 
            { 
                SqlCommand myCommand = new SqlCommand(databaseOwner + ".cs_Sections_MoveOrReOrder", myConnection); 

                // Mark the Command as a SPROC 
                myCommand.CommandType = CommandType.StoredProcedure; 

                // Pass sproc parameters 
                myCommand.Parameters.Add("@NewGroupID", SqlDbType.Int).Value = GroupID; 
                myCommand.Parameters.Add("@SectionID", SqlDbType.Int).Value = SectionID; 
                myCommand.Parameters.Add("@SectionIndex", SqlDbType.Int).Value = Index; 
                myCommand.Parameters.Add(this.SettingsIDParameter());

                // Execute the command 
                myConnection.Open(); 
                myCommand.ExecuteNonQuery(); 

                myConnection.Close(); 
            }
        }


        /*********************************************************************************/

        /**************** SUMMARY FUNCTIONS **************
                 * This function is used to get Summary information about WebForums.NET
                 * *********************************************************/

        #region #### Site Statistics ####

		public override void CalculateSiteStatistics(int settingsID, int updateWindow)
		{
			using( SqlConnection myConnection = GetSqlConnection() ) 
			{
				using(SqlCommand myCommand = new SqlCommand(databaseOwner + ".cs_system_UpdateSite", myConnection))
				{

					// Mark the Command as a SPROC
					myCommand.CommandType = CommandType.StoredProcedure;

					// Parameters
					//
					myCommand.Parameters.Add("@SettingsID",SqlDbType.Int,4).Value = settingsID;
					myCommand.Parameters.Add("@UpdateWindow", SqlDbType.Int).Value = updateWindow;


					// Execute the command
					myConnection.Open();
					myCommand.ExecuteNonQuery();
					myConnection.Close();
				}

			}
		}
    
        /// <summary>
        /// Returns a SummaryObject object with summary information about the message board.
        /// </summary>
        /// <returns>A SummaryObject object populated with the summary information.</returns>
        public override SiteStatistics LoadSiteStatistics(int settingsID) 
        {
            // Create Instance of Connection and Command Object
			using( SqlConnection myConnection = GetSqlConnection() ) 
			{
				using(SqlCommand myCommand = new SqlCommand(databaseOwner + ".cs_Site_Statistics", myConnection))
				{
					// Mark the Command as a SPROC
					myCommand.CommandType = CommandType.StoredProcedure;
					myCommand.Parameters.Add("@SettingsID",SqlDbType.Int,4).Value = settingsID;

					// Execute the command
					myConnection.Open();
                    SiteStatistics statistics = new SiteStatistics();   
                    using(SqlDataReader dr = myCommand.ExecuteReader(CommandBehavior.CloseConnection))
                    {
                                

                        try 
                        {

                            if(dr.Read())
                            {
                                statistics.TotalUsers =             (int) dr["TotalUsers"];
                                statistics.CurrentAnonymousUsers =  (int) dr["CurrentAnonymousUsers"];
                                statistics.TotalPosts =             (int) dr["TotalPosts"];
                                statistics.TotalModerators =        (int) dr["TotalModerators"];
                                statistics.TotalModeratedPosts =    (int) dr["TotalModeratedPosts"];
                                statistics.TotalThreads =           (int) dr["TotalTopics"];
                                statistics.TotalAnonymousUsers =    (int) dr["TotalAnonymousUsers"];
                                statistics.NewPostsInPast24Hours =  (int) dr["NewPostsInPast24Hours"];
                                statistics.NewThreadsInPast24Hours =(int) dr["NewThreadsInPast24Hours"];
                                statistics.NewUsersInPast24Hours =  (int) dr["NewUsersInPast24Hours"];
                                statistics.MostViewsPostID =        (int) dr["MostViewsPostID"];
                                statistics.MostViewsSubject =       (string) dr["MostViewsSubject"];
                                statistics.MostActivePostID =       (int) dr["MostActivePostID"];
                                statistics.MostActiveSubject =      (string) dr["MostActiveSubject"];
                                statistics.MostReadPostID =         (int) dr["MostReadPostID"];
                                statistics.MostReadPostSubject =    (string) dr["MostReadSubject"];
                                statistics.MostActiveUser =         (string) dr["MostActiveUser"];
                                statistics.MostActiveUserID =       (int) dr["MostActiveUserID"];
                                statistics.NewestUser =             (string) dr["NewestUser"];
                                statistics.NewestUserID =           (int) dr["NewestUserID"];
						
                                if (dr.NextResult()) 
                                {
                                    while (dr.Read())
                                        statistics.ActiveUsers.Add( cs_PopulateUserFromIDataReader(dr) );
                                }

								if (dr.NextResult())
								{
									while (dr.Read())
									{
										User user = cs_PopulateUserFromIDataReader(dr);
										user.PostsModerated = (int) dr["PostsModerated"];
										statistics.ActiveModerators.Add(user);
									}
								}

								if (dr.NextResult())
								{
									while (dr.Read())
									{
										ModerationAuditSummary item = new ModerationAuditSummary();
										item.Action = (string) dr["Description"];
										item.Total = (int) dr["TotalActions"];
										statistics.ModerationActions.Add(item);
									}
								}
                            }
                        } 
                        catch 
                        {

                            myConnection.Close();
                            return statistics;
                        }
                        dr.Close();
                    }
					myConnection.Close();

					return statistics;
				}
			}
        }

        #endregion

        /*********************************************************************************/

		#region Censorship Functions

		public override ArrayList GetCensors() {
			ArrayList censorShips = new ArrayList();

			// Create Instance of Connection and Command Object
			using( SqlConnection myConnection = GetSqlConnection() ) {
				SqlCommand myCommand = new SqlCommand(databaseOwner + ".cs_Censorships_Get", myConnection);

				// Mark the Command as a SPROC
				myCommand.CommandType = CommandType.StoredProcedure;

                myCommand.Parameters.Add(this.SettingsIDParameter());

				// Add Parameters to SPROC
				//			myCommand.Parameters.Add("@Word", SqlDbType.Int).Value = censoredWord;

				// Open the database connection and execute the command
				SqlDataReader dr;

				myConnection.Open();
                using(dr = myCommand.ExecuteReader(CommandBehavior.CloseConnection | CommandBehavior.SingleResult))
                {

                    while (dr.Read())
                        censorShips.Add( PopulateCenshorFromIDataReader(dr) );

                    dr.Close();
                }
				myConnection.Close();

				return censorShips;
			}
		}

		public override int CreateUpdateDeleteCensor( Censor censorship, DataProviderAction action ) {
			// Create Instance of Connection and Command Object
			using( SqlConnection myConnection = GetSqlConnection() ) {
				SqlCommand myCommand = new SqlCommand(databaseOwner + ".cs_Censorship_CreateUpdateDelete", myConnection);
				//			string word = "";

				// Mark the Command as a SPROC
				myCommand.CommandType = CommandType.StoredProcedure;

				// Add Parameters to SPROC
				//
//				if (action == DataProviderAction.Create)
//					myCommand.Parameters.Add("@Word", SqlDbType.NVarChar, 30);//.Direction = ParameterDirection.Output;
//				else
					myCommand.Parameters.Add("@Word", SqlDbType.NVarChar, 30).Value = censorship.Word;
                myCommand.Parameters.Add(this.SettingsIDParameter());

				if( action == DataProviderAction.Delete )
					myCommand.Parameters.Add("@DeleteWord", SqlDbType.Bit).Value	= 1;
				else
					myCommand.Parameters.Add("@DeleteWord", SqlDbType.Bit).Value	= 0;

				myCommand.Parameters.Add("@Replacement", SqlDbType.NVarChar, 30).Value = censorship.Replacement;

				// Open the connection
				myConnection.Open();
				myCommand.ExecuteNonQuery();

//				if (action == DataProviderAction.Create)
//					forumGroupID = (int) myCommand.Parameters["@GroupID"].Value;

				myConnection.Close();

				return 1;
			}
		}

		#endregion

		#region Ranking Functions

		public override ArrayList GetRanks() {
			ArrayList ranks = new ArrayList();

			// Create Instance of Connection and Command Object
			using( SqlConnection myConnection = GetSqlConnection() ) {
				SqlCommand myCommand = new SqlCommand(databaseOwner + ".cs_Ranks_Get", myConnection);

				// Mark the Command as a SPROC
				myCommand.CommandType = CommandType.StoredProcedure;

				// Add Parameters to SPROC
				//			myCommand.Parameters.Add("@UserId", SqlDbType.Int).Value = 0;

                myCommand.Parameters.Add(this.SettingsIDParameter());
				// Open the database connection and execute the command
				SqlDataReader dr;

				myConnection.Open();
                using(dr = myCommand.ExecuteReader(CommandBehavior.CloseConnection | CommandBehavior.SingleResult))
                {

                    while (dr.Read())
                        ranks.Add( PopulateRankFromIDataReader(dr) );

                    dr.Close();
                }
				myConnection.Close();

				return ranks;
			}
		}

		public override int CreateUpdateDeleteRank( Rank rank, DataProviderAction action ) {
			// Create Instance of Connection and Command Object
			using( SqlConnection myConnection = GetSqlConnection() ) {
				SqlCommand myCommand = new SqlCommand(databaseOwner + ".cs_Rank_CreateUpdateDelete", myConnection);
				int rankId = 0;

				// Mark the Command as a SPROC
				myCommand.CommandType = CommandType.StoredProcedure;

				// Add Parameters to SPROC
				//
				if (action == DataProviderAction.Create)
					myCommand.Parameters.Add("@RankID", SqlDbType.Int).Direction = ParameterDirection.Output;
				else
					myCommand.Parameters.Add("@RankID", SqlDbType.Int).Value = rank.RankId;

				if( action == DataProviderAction.Delete)
					myCommand.Parameters.Add("@DeleteRank", SqlDbType.Bit).Value = 1;
				else
					myCommand.Parameters.Add("@DeleteRank", SqlDbType.Bit).Value = 0;

                myCommand.Parameters.Add(this.SettingsIDParameter());
				myCommand.Parameters.Add("@RankName", SqlDbType.NVarChar, 60).Value		= rank.RankName;
				myCommand.Parameters.Add("@PostingCountMin", SqlDbType.Int).Value		= rank.PostingCountMinimum;
				myCommand.Parameters.Add("@PostingCountMax", SqlDbType.Int).Value		= rank.PostingCountMaximum;
				myCommand.Parameters.Add("@RankIconUrl", SqlDbType.NVarChar, 512).Value	= rank.RankIconUrl;
		

				// Open the connection
				myConnection.Open();
				myCommand.ExecuteNonQuery();
                myConnection.Close();

                if (action == DataProviderAction.Create)
					rankId = (int) myCommand.Parameters["@RankID"].Value;


				return rankId;
			}
		}

		#endregion

		#region Reporting Functions

		public override ArrayList GetReports(int reportId, bool ignorePermissions) {
			ArrayList reports = new ArrayList();

			// Create Instance of Connection and Command Object
			using( SqlConnection myConnection = GetSqlConnection() ) {
				SqlCommand myCommand = new SqlCommand(databaseOwner + ".cs_Reports_Get", myConnection);

				// Mark the Command as a SPROC
				myCommand.CommandType = CommandType.StoredProcedure;

				// Add Parameters to SPROC
				myCommand.Parameters.Add("@ReportID", SqlDbType.Int).Value = reportId;
                myCommand.Parameters.Add(this.SettingsIDParameter());

				// Open the database connection and execute the command
				SqlDataReader dr;

				myConnection.Open();
                using(dr = myCommand.ExecuteReader(CommandBehavior.CloseConnection | CommandBehavior.SingleResult))
                {

                    while (dr.Read())
                        reports.Add( PopulateReportFromIDataReader(dr) );

                    dr.Close();
                }
				myConnection.Close();

				return reports;
			}
		}

		public override int CreateUpdateDeleteReport( Report report, DataProviderAction action ) {
			// Create Instance of Connection and Command Object
			using( SqlConnection myConnection = GetSqlConnection() ) {
				SqlCommand myCommand = new SqlCommand(databaseOwner + ".cs_Report_CreateUpdateDelete", myConnection);
				int reportId = 0;

				// Mark the Command as a SPROC
				myCommand.CommandType = CommandType.StoredProcedure;

				// Add Parameters to SPROC
				//
				if (action == DataProviderAction.Create)
					myCommand.Parameters.Add("@ReportID", SqlDbType.Int).Direction = ParameterDirection.Output;
				else
					myCommand.Parameters.Add("@ReportID", SqlDbType.Int).Value = report.ReportId;

				if( action == DataProviderAction.Delete)
					myCommand.Parameters.Add("@DeleteReport", SqlDbType.Bit).Value = 1;
				else
					myCommand.Parameters.Add("@DeleteReport", SqlDbType.Bit).Value = 0;

				myCommand.Parameters.Add("@ReportName"		, SqlDbType.NVarChar, 20).Value		= report.ReportName;
				myCommand.Parameters.Add("@Active"			, SqlDbType.TinyInt).Value			= report.IsActive == true ? 1 : 0;
				myCommand.Parameters.Add("@ReportCommand"	, SqlDbType.NVarChar, 8000).Value	= report.ReportCommand;
				myCommand.Parameters.Add("@ReportScript"	, SqlDbType.NText).Value			= report.ReportScript;
                myCommand.Parameters.Add(this.SettingsIDParameter());
		

				// Open the connection
				myConnection.Open();
				myCommand.ExecuteNonQuery();

				if (action == DataProviderAction.Create)
					reportId = (int) myCommand.Parameters["@ReportID"].Value;

				myConnection.Close();

				return reportId;
			}
		}

		#endregion

		#region Services Functions

		public override ArrayList GetServices(int serviceId, bool ignorePermissions) {
			ArrayList services = new ArrayList();

			// Create Instance of Connection and Command Object
			using( SqlConnection myConnection = GetSqlConnection() ) {
				SqlCommand myCommand = new SqlCommand(databaseOwner + ".cs_Services_Get", myConnection);

				// Mark the Command as a SPROC
				myCommand.CommandType = CommandType.StoredProcedure;

				// Add Parameters to SPROC
				myCommand.Parameters.Add("@ServiceID", SqlDbType.Int).Value = serviceId;
                myCommand.Parameters.Add(this.SettingsIDParameter());

				// Open the database connection and execute the command
				SqlDataReader dr;

				myConnection.Open();
                using(dr = myCommand.ExecuteReader(CommandBehavior.CloseConnection | CommandBehavior.SingleResult))
                {
                    while (dr.Read())
                        services.Add( PopulateServiceFromIDataReader(dr) );

                    dr.Close();
                }
				myConnection.Close();

				return services;
			}
		}

		public override int CreateUpdateDeleteService( Service service, DataProviderAction action ) {
			// Create Instance of Connection and Command Object
			using( SqlConnection myConnection = GetSqlConnection() ) {
				SqlCommand myCommand = new SqlCommand(databaseOwner + ".cs_Service_CreateUpdateDelete", myConnection);
				int serviceId = 0;

				// Mark the Command as a SPROC
				myCommand.CommandType = CommandType.StoredProcedure;

				// Add Parameters to SPROC
				//
				if (action == DataProviderAction.Create)
					myCommand.Parameters.Add("@ServiceID", SqlDbType.Int).Direction = ParameterDirection.Output;
				else
					myCommand.Parameters.Add("@ServiceID", SqlDbType.Int).Value = service.ServiceId;

				if( action == DataProviderAction.Delete)
					myCommand.Parameters.Add("@DeleteService", SqlDbType.Bit).Value = 1;
				else
					myCommand.Parameters.Add("@DeleteService", SqlDbType.Bit).Value = 0;

				myCommand.Parameters.Add("@ServiceName"				, SqlDbType.NVarChar, 60).Value		= service.ServiceName;
				myCommand.Parameters.Add("@ServiceTypeCode"			, SqlDbType.Int).Value				= service.ServiceCode;
				myCommand.Parameters.Add("@ServiceAssemblyPath"		, SqlDbType.NVarChar, 512).Value	= service.ServiceAssemblyPath;
				myCommand.Parameters.Add("@ServiceFullClassName"	, SqlDbType.NVarChar, 512).Value	= service.ServiceFullClassName;
				myCommand.Parameters.Add("@ServiceWorkingDirectory"	, SqlDbType.NVarChar, 512).Value	= service.ServiceWorkingDirectory;
		
                myCommand.Parameters.Add(this.SettingsIDParameter());
		

				// Open the connection
				myConnection.Open();
				myCommand.ExecuteNonQuery();
                myConnection.Close();

				if (action == DataProviderAction.Create)
					serviceId = (int) myCommand.Parameters["@ServiceID"].Value;

				

				return serviceId;
			}
		}

		#endregion

		#region Smiley Functions

		public override ArrayList GetSmilies() {
			ArrayList smilies = new ArrayList();

			// Create Instance of Connection and Command Object
			using( SqlConnection myConnection = GetSqlConnection() ) {
				SqlCommand myCommand = new SqlCommand(databaseOwner + ".cs_Smilies_Get", myConnection);

				// Mark the Command as a SPROC
				myCommand.CommandType = CommandType.StoredProcedure;

				//			// Add Parameters to SPROC
							myCommand.Parameters.Add(this.SettingsIDParameter());

				// Open the database connection and execute the command
				SqlDataReader dr;

				myConnection.Open();
                using(dr = myCommand.ExecuteReader(CommandBehavior.CloseConnection | CommandBehavior.SingleResult))
                {

                    while (dr.Read())
                        smilies.Add( PopulateSmileyFromIDataReader(dr) );

                    dr.Close();
                }
				myConnection.Close();

				return smilies;
			}
		}

		public override int CreateUpdateDeleteSmiley( Smiley smiley, DataProviderAction action ) {
			// Create Instance of Connection and Command Object
			using( SqlConnection myConnection = GetSqlConnection() ) {
				SqlCommand myCommand = new SqlCommand(databaseOwner + ".cs_Smiley_CreateUpdateDelete", myConnection);
				int smileyId = 0;

				// Mark the Command as a SPROC
				myCommand.CommandType = CommandType.StoredProcedure;

				// Add Parameters to SPROC
				//
				if (action == DataProviderAction.Create)
					myCommand.Parameters.Add("@SmileyID", SqlDbType.Int).Direction = ParameterDirection.Output;
				else
					myCommand.Parameters.Add("@SmileyID", SqlDbType.Int).Value = smiley.SmileyId;

				if( action == DataProviderAction.Delete)
					myCommand.Parameters.Add("@DeleteSmiley", SqlDbType.Bit).Value = 1;
				else
					myCommand.Parameters.Add("@DeleteSmiley", SqlDbType.Bit).Value = 0;

				myCommand.Parameters.Add("@SmileyCode"	, SqlDbType.NVarChar, 20).Value		= smiley.SmileyCode;
				myCommand.Parameters.Add("@SmileyUrl"	, SqlDbType.NVarChar, 512).Value	= smiley.SmileyUrl;
				myCommand.Parameters.Add("@SmileyText"	, SqlDbType.NVarChar, 512).Value	= smiley.SmileyText;
				myCommand.Parameters.Add("@BracketSafe" , SqlDbType.Bit ).Value				= smiley.IsSafeWithoutBrackets() ? 1 : 0 ;
                myCommand.Parameters.Add(this.SettingsIDParameter());
		

				// Open the connection
				myConnection.Open();
				myCommand.ExecuteNonQuery();
				myConnection.Close();

				if (action == DataProviderAction.Create)
					smileyId = (int) myCommand.Parameters["@SmileyID"].Value;



				return smileyId;
			}
		}

		#endregion

		#region Style Functions

		public override ArrayList GetStyles(int styleId, bool ignorePermissions) {
			ArrayList styles = new ArrayList();

			// Create Instance of Connection and Command Object
			using( SqlConnection myConnection = GetSqlConnection() ) {
				SqlCommand myCommand = new SqlCommand(databaseOwner + ".cs_Styles_Get", myConnection);

				// Mark the Command as a SPROC
				myCommand.CommandType = CommandType.StoredProcedure;

				// Add Parameters to SPROC
				myCommand.Parameters.Add("@StyleID", SqlDbType.Int).Value = styleId;
                myCommand.Parameters.Add(this.SettingsIDParameter());

				// Open the database connection and execute the command
				SqlDataReader dr;

				myConnection.Open();
                using(dr = myCommand.ExecuteReader(CommandBehavior.CloseConnection | CommandBehavior.SingleResult))
                {

                    while (dr.Read())
                        styles.Add( PopulateStyleFromIDataReader(dr) );

                    dr.Close();
                }
				myConnection.Close();

				return styles;
			}
		}

		public override int CreateUpdateDeleteStyle( Style style, DataProviderAction action ) {
			// Create Instance of Connection and Command Object
			using( SqlConnection myConnection = GetSqlConnection() ) {
				SqlCommand myCommand = new SqlCommand(databaseOwner + ".cs_Style_CreateUpdateDelete", myConnection);
				int styleId = 0;

				// Mark the Command as a SPROC
				myCommand.CommandType = CommandType.StoredProcedure;

                myCommand.Parameters.Add(this.SettingsIDParameter());

				// Add Parameters to SPROC
				//
				if (action == DataProviderAction.Create)
					myCommand.Parameters.Add("@StyleID", SqlDbType.Int).Direction = ParameterDirection.Output;
				else
					myCommand.Parameters.Add("@StyleID", SqlDbType.Int).Value = style.StyleId;

				if( action == DataProviderAction.Delete)
					myCommand.Parameters.Add("@DeleteRank", SqlDbType.Bit).Value = 1;
				else
					myCommand.Parameters.Add("@DeleteRank", SqlDbType.Bit).Value = 0;

				myCommand.Parameters.Add("@StyleName"			, SqlDbType.VarChar, 30).Value	= style.StyleName;
				myCommand.Parameters.Add("@StyleSheetTemplate"	, SqlDbType.VarChar, 30).Value	= style.StyleSheetTemplate;
				myCommand.Parameters.Add("@BodyBackgroundColor"	, SqlDbType.Int).Value			= style.BodyBackgroundColor;
				myCommand.Parameters.Add("@BodyTextColor"		, SqlDbType.Int).Value			= style.BodyTextColor;
				myCommand.Parameters.Add("@LinkVisited"			, SqlDbType.Int).Value			= style.LinkVisited;
				myCommand.Parameters.Add("@LinkHover"			, SqlDbType.Int).Value			= style.LinkHover;
				myCommand.Parameters.Add("@LinkActive"			, SqlDbType.Int).Value			= style.LinkActive;
				myCommand.Parameters.Add("@RowColorPrimary"		, SqlDbType.Int).Value			= style.RowColorPrimary;
				myCommand.Parameters.Add("@RowColorSecondary"	, SqlDbType.Int).Value			= style.RowColorSecondary;
				myCommand.Parameters.Add("@RowColorTertiary"	, SqlDbType.Int).Value			= style.RowColorTertiary;
				myCommand.Parameters.Add("@RowClassPrimary"		, SqlDbType.VarChar, 30).Value	= style.RowClassPrimary;
				myCommand.Parameters.Add("@RowClassSecondary"	, SqlDbType.VarChar, 30).Value	= style.RowClassSecondary;
				myCommand.Parameters.Add("@RowClassTertiary"	, SqlDbType.VarChar, 30).Value	= style.RowClassTertiary;
				myCommand.Parameters.Add("@HeaderColorPrimary"	, SqlDbType.Int).Value			= style.HeaderColorPrimary;
				myCommand.Parameters.Add("@HeaderColorSecondary", SqlDbType.Int).Value			= style.HeaderColorSecondary;
				myCommand.Parameters.Add("@HeaderColorTertiary"	, SqlDbType.Int).Value			= style.HeaderColorTertiary;
				myCommand.Parameters.Add("@HeaderStylePrimary"	, SqlDbType.VarChar, 30).Value	= style.HeaderStylePrimary;
				myCommand.Parameters.Add("@HeaderStyleSecondary", SqlDbType.VarChar, 30).Value	= style.HeaderStyleSecondary;
				myCommand.Parameters.Add("@HeaderStyleTertiary"	, SqlDbType.VarChar, 30).Value	= style.HeaderStyleTertiary;
				myCommand.Parameters.Add("@CellColorPrimary"	, SqlDbType.Int).Value			= style.CellColorPrimary;
				myCommand.Parameters.Add("@CellColorSecondary"	, SqlDbType.Int).Value			= style.CellColorSecondary;
				myCommand.Parameters.Add("@CellColorTertiary"	, SqlDbType.Int).Value			= style.CellColorTertiary;
				myCommand.Parameters.Add("@CellClassPrimary"	, SqlDbType.VarChar, 30).Value	= style.CellClassPrimary;
				myCommand.Parameters.Add("@CellClassSecondary"	, SqlDbType.VarChar, 30).Value	= style.CellClassSecondary;
				myCommand.Parameters.Add("@CellClassTertiary"	, SqlDbType.VarChar, 30).Value	= style.CellClassTertiary;
				myCommand.Parameters.Add("@FontFacePrimary"		, SqlDbType.VarChar, 30).Value	= style.FontFacePrimary;
				myCommand.Parameters.Add("@FontFaceSecondary"	, SqlDbType.VarChar, 30).Value	= style.FontFaceSecondary;
				myCommand.Parameters.Add("@FontFaceTertiary"	, SqlDbType.VarChar, 30).Value	= style.FontFaceTertiary;
				myCommand.Parameters.Add("@FontSizePrimary"		, SqlDbType.SmallInt).Value		= style.FontSizePrimary;
				myCommand.Parameters.Add("@FontSizeSecondary"	, SqlDbType.SmallInt).Value		= style.FontSizeSecondary;
				myCommand.Parameters.Add("@FontSizeTertiary"	, SqlDbType.SmallInt).Value		= style.FontSizeTertiary;
				myCommand.Parameters.Add("@FontColorPrimary"	, SqlDbType.Int).Value			= style.FontColorPrimary;
				myCommand.Parameters.Add("@FontColorSecondary"	, SqlDbType.Int).Value			= style.FontColorSecondary;
				myCommand.Parameters.Add("@FontColorTertiary"	, SqlDbType.Int).Value			= style.FontColorTertiary;
				myCommand.Parameters.Add("@SpanClassPrimary"	, SqlDbType.VarChar, 30).Value	= style.SpanClassPrimary;
				myCommand.Parameters.Add("@SpanClassSecondary"	, SqlDbType.VarChar, 30).Value	= style.SpanClassSecondary;
				myCommand.Parameters.Add("@SpanClassTertiary"	, SqlDbType.VarChar, 30).Value	= style.SpanClassTertiary;

		

				// Open the connection
				myConnection.Open();
				myCommand.ExecuteNonQuery();
                myConnection.Close();

				if (action == DataProviderAction.Create)
					styleId = (int) myCommand.Parameters["@StyleID"].Value;

				
				return styleId;
			}
		}

		#endregion

        #region Public Static Helper Functions
        public static DateTime GetSafeSqlDateTime (DateTime date) {

            if (date == DateTime.MinValue)
                return (DateTime) SqlDateTime.MinValue;
            return date;

        }
        #endregion

        #region Audit
        public override AuditSet GetAuditHistoryForPost (int postID, int pageIndex, int pageSize, bool returnRecordCount) {
            // Create Instance of Connection and Command Object
            //
            using (SqlConnection myConnection = GetSqlConnection()) {
                SqlCommand myCommand = new SqlCommand( databaseOwner + ".cs_ModerationAudit_Message_Get", myConnection );
                myCommand.CommandType = CommandType.StoredProcedure;
                AuditSet auditSet = new AuditSet();

                // Set parameters
                //
                myCommand.Parameters.Add( "@PostID", SqlDbType.Int ).Value            = postID;
                myCommand.Parameters.Add( "@PageIndex", SqlDbType.Int ).Value         = pageIndex;
                myCommand.Parameters.Add( "@PageSize", SqlDbType.Int ).Value          = SQLHelper.GetSafeSqlInt(pageSize) ;
                myCommand.Parameters.Add( "@ReturnRecordCount", SqlDbType.Bit ).Value = returnRecordCount;
                myCommand.Parameters.Add( this.SettingsIDParameter() );

                // Execute the command
                //
                myConnection.Open();
                using(SqlDataReader reader = myCommand.ExecuteReader(CommandBehavior.CloseConnection))
                {

                    // Get the results
                    //
                    while (reader.Read())
                        auditSet.Records.Add( PopulateAuditItemFromIDataReader( reader ) );

                    // Are we expecting more results?
                    //
                    if (returnRecordCount && reader.NextResult()) 
                    {
                        reader.Read();

                        // Read the value
                        //
                        auditSet.TotalRecords = (int) reader[0];
                    }

                    reader.Close();
                }

                myConnection.Close();

                return auditSet;
            }
        }

        public override AuditSet GetAuditHistoryForUser (int userID, ModeratorActions action, int pageIndex, int pageSize, bool returnRecordCount) {
            // Create Instance of Connection and Command Object
            //
            using (SqlConnection myConnection = GetSqlConnection()) {
                SqlCommand myCommand = new SqlCommand( databaseOwner + ".cs_ModerationAudit_User_Get", myConnection );
                myCommand.CommandType = CommandType.StoredProcedure;
                AuditSet auditSet = new AuditSet();

                // Set parameters
                //
                myCommand.Parameters.Add( "@UserID", SqlDbType.Int ).Value                  = userID;
                myCommand.Parameters.Add( "@ModerationAction", SqlDbType.Int ).Value        = (int) action;
                myCommand.Parameters.Add( "@PageIndex", SqlDbType.Int ).Value               = pageIndex;
                myCommand.Parameters.Add( "@PageSize", SqlDbType.Int ).Value                = SQLHelper.GetSafeSqlInt(pageSize) ;
                myCommand.Parameters.Add( "@ReturnRecordCount", SqlDbType.Bit ).Value       = returnRecordCount;
                myCommand.Parameters.Add( this.SettingsIDParameter() );

                // Execute the command
                //
                myConnection.Open();
                using(SqlDataReader reader = myCommand.ExecuteReader(CommandBehavior.CloseConnection))
                {

                    // Get the results
                    //
                    while (reader.Read())
                        auditSet.Records.Add( PopulateAuditItemFromIDataReader( reader ) );

                    // Are we expecting more results?
                    //
                    if (returnRecordCount && reader.NextResult()) 
                    {
                        reader.Read();

                        // Read the value
                        //
                        auditSet.TotalRecords = (int) reader[0];
                    }
                    reader.Close();
                }
                myConnection.Close();

                return auditSet;
            }
        }
        
        public override AuditSummary GetAuditSummary (int postID, int userID) { 
            // Create Instance of Connection and Command Object
            //
            using (SqlConnection myConnection = GetSqlConnection()) 
            {
                SqlCommand myCommand = new SqlCommand(databaseOwner + ".cs_ModerationAudit_Summary_Get", myConnection);
                SqlDataReader reader;
                AuditSummary summary = new AuditSummary();

                // Mark the Command as a SPROC
                myCommand.CommandType = CommandType.StoredProcedure;

                // Add Parameters to SPROC
                if (postID <= 0 && userID > 0) 
                {
                    myCommand.Parameters.Add("@PostID", SqlDbType.Int, 4).Value = DBNull.Value;
                    myCommand.Parameters.Add("@UserID", SqlDbType.Int, 4).Value = userID;
                } 
                if (postID > 0 && userID <= 0) 
                {
                    myCommand.Parameters.Add("@PostID", SqlDbType.Int, 4).Value = postID;
                    myCommand.Parameters.Add("@UserID", SqlDbType.Int, 4).Value = DBNull.Value;
                }
                myCommand.Parameters.Add(this.SettingsIDParameter());

                // Execute the command
                myConnection.Open();

                using(reader = myCommand.ExecuteReader(CommandBehavior.CloseConnection | CommandBehavior.SingleResult))
                {
                
                    while (reader.Read()) 
                    {
                        ModeratorActions mAction = (ModeratorActions) Convert.ToInt32( reader["ModerationAction"] );
                        int total = (reader["Total"] == DBNull.Value) ? 0 : Convert.ToInt32( reader["Total"] );

                        summary[mAction] = total;
                    }

                    reader.Close();
                }
                myConnection.Close();

                return summary;
            }
        }

        public override void SaveUserAuditEvent (ModerateUserSetting auditFlag, User user, int moderatorID) {

            using (SqlConnection myConnection = GetSqlConnection()) {
                SqlCommand myCommand = new SqlCommand(databaseOwner + ".cs_ModerationAudit_User_SaveEvent", myConnection);

                // Mark the Command as a SPROC
                myCommand.CommandType = CommandType.StoredProcedure;

                // Add Parameters to SPROC
                myCommand.Parameters.Add("@UserID", SqlDbType.Int).Value = user.UserID;
                myCommand.Parameters.Add("@ModeratorID", SqlDbType.Int).Value = moderatorID;
                myCommand.Parameters.Add("@PasswordChanged", SqlDbType.Bit).Value = false;
                myCommand.Parameters.Add("@PasswordReset", SqlDbType.Bit).Value = false;
                myCommand.Parameters.Add("@UserBanned", SqlDbType.Bit).Value = false;
                myCommand.Parameters.Add("@BanDetails", SqlDbType.NVarChar, 1024).Value = "";
                myCommand.Parameters.Add("@UserUnbanned", SqlDbType.Bit).Value = false;
                myCommand.Parameters.Add("@UserEdited", SqlDbType.Bit).Value = false;                
                myCommand.Parameters.Add("@UserModerated", SqlDbType.Bit).Value = false;
		myCommand.Parameters.Add("@UserIgnored", SqlDbType.Bit).Value = false;
		myCommand.Parameters.Add("@UserUnignored", SqlDbType.Bit).Value = false;

                myCommand.Parameters.Add(this.SettingsIDParameter());

                switch (auditFlag) {                    
                    case ModerateUserSetting.PasswordChanged:
                        myCommand.Parameters["@PasswordChanged"].Value = true;
                        break;

                    case ModerateUserSetting.PasswordReset:
                        myCommand.Parameters["@PasswordReset"].Value = true;
                        break;

                    case ModerateUserSetting.UserBanned:
                        myCommand.Parameters["@UserBanned"].Value = true;
                        myCommand.Parameters["@BanDetails"].Value = string.Format( ResourceManager.GetString( "Utility_BanDetails" ), Formatter.FormatDate( user.BannedUntil, true ), Formatter.FormatBanReason( user.BanReason ) );
                        break;

                    case ModerateUserSetting.UserUnbanned:
                        myCommand.Parameters["@UserUnbanned"].Value = true;
                        break;

                    case ModerateUserSetting.UserEdited:
                        myCommand.Parameters["@UserEdited"].Value = true;
                        break;

                    case ModerateUserSetting.UserModerated:
                        myCommand.Parameters["@UserModerated"].Value = true;
                        break;

                    case ModerateUserSetting.UserUnmoderated:
                        myCommand.Parameters["@UserModerated"].Value = false;
                        break;
		    case ModerateUserSetting.UserIgnored:
			myCommand.Parameters["@UserIgnored"].Value = true;
			break;
		    case ModerateUserSetting.UserUnignored:
			myCommand.Parameters["@UserUnignored"].Value = true;
			break;
   
                }

                // Execute the command
                myConnection.Open();
                myCommand.ExecuteNonQuery();
                myConnection.Close();
            }
        }

        #endregion

		#region Content
		/****************************************************************
		// CreateUpdateDeleteContent
		//
		/// <summary>
		/// Creates, updates or deletes content
		/// </summary>
		/// 
		****************************************************************/
		public override int CreateUpdateDeleteContent(Content content, DataProviderAction action) 
		{
			// Create Instance of Connection and Command Object
			using( SqlConnection myConnection = GetSqlConnection() ) 
			{
				SqlCommand myCommand = new SqlCommand(databaseOwner + ".cs_Content_CreateUpdateDelete", myConnection);
				int contentID = content.ID;

				// Mark the Command as a SPROC
				myCommand.CommandType = CommandType.StoredProcedure;

				// Add Parameters to SPROC
				if (action == DataProviderAction.Create)
					myCommand.Parameters.Add("@ContentID", SqlDbType.Int).Direction = ParameterDirection.Output;
				else
					myCommand.Parameters.Add("@ContentID", SqlDbType.Int).Value = contentID;

				myCommand.Parameters.Add("@Action", SqlDbType.Int).Value = action;
				myCommand.Parameters.Add("@Name", SqlDbType.VarChar, 256).Value = content.Name;
				myCommand.Parameters.Add("@Title", SqlDbType.NVarChar, 256).Value = content.Title;
				myCommand.Parameters.Add("@Body",SqlDbType.NText).Value = content.Body;
                myCommand.Parameters.Add("@FormattedBody",SqlDbType.NText).Value = content.FormattedBody;
				myCommand.Parameters.Add("@Hidden", SqlDbType.Bit).Value = content.Hidden;
				myCommand.Parameters.Add("@UpdateDate", SqlDbType.Bit).Value = true;
				myCommand.Parameters.Add(this.SettingsIDParameter());

				// Open the connection
				myConnection.Open();
				myCommand.ExecuteNonQuery();
				myConnection.Close();

				if (action == DataProviderAction.Create)
					contentID = (int) myCommand.Parameters["@ContentID"].Value;

				return contentID;
			}
		}

		/****************************************************************
		// GetContent
		//
		/// <summary>
		/// returns content for the given ID
		/// </summary>
		/// 
		****************************************************************/
        public override Content GetContent(int id, string name)
		{
			// Create Instance of Connection and Command Object
			using( SqlConnection myConnection = GetSqlConnection() ) 
			{
				using (SqlCommand myCommand = new SqlCommand(databaseOwner + ".cs_Content_Load", myConnection))
				{

					// Mark the Command as a SPROC
					myCommand.CommandType = CommandType.StoredProcedure;

					// Add Parameters to SPROC
					myCommand.Parameters.Add("@ContentID", SqlDbType.Int).Value = id;
                    myCommand.Parameters.Add("@Name", SqlDbType.NVarChar).Value = name;
                    myCommand.Parameters.Add(this.SettingsIDParameter());

                    Content content = null;
					// Open the connection
					myConnection.Open();

                    using(SqlDataReader reader = myCommand.ExecuteReader(CommandBehavior.CloseConnection | CommandBehavior.SingleRow))
					{
						if(reader.Read())
						{
                            content = LoadContnet(reader);
						}
						reader.Close();
					}

                    return content;
				}
			}		
		}

        private static Content LoadContnet(SqlDataReader reader)
        {
            return new Content((int)reader["ContentID"], reader["Name"] as string, reader["Title"] as string, reader["Body"] as string, reader["FormattedBody"] as string, (DateTime)reader["LastModified"], (bool)reader["Hidden"]);
        }

		/****************************************************************
		// GetContentList
		//
		/// <summary>
		/// returns a list of listable content
		/// </summary>
		/// 
		****************************************************************/
		public override IList GetContentList()
		{
			// Create Instance of Connection and Command Object
			using( SqlConnection myConnection = GetSqlConnection() ) 
			{
				ArrayList list = new ArrayList();
				SqlCommand myCommand = new SqlCommand(databaseOwner + ".cs_Content_GetList", myConnection);

				// Mark the Command as a SPROC
				myCommand.CommandType = CommandType.StoredProcedure;

                myCommand.Parameters.Add(this.SettingsIDParameter());


                myConnection.Open();
				using(SqlDataReader reader = myCommand.ExecuteReader(CommandBehavior.CloseConnection|CommandBehavior.SingleResult))
				{
				    while(reader.Read())
				    {
				        list.Add(LoadContnet(reader));
				    }
				}

				myConnection.Close();

				return list;
			}
		}
		#endregion
    }
}
